Hi,
I have 3 tables Description, Item, Transaction
Description Item Transaction
DeID Name IID Name DeID TranNo Type IID Date
1 Printer 1 Stylus T10 1 1 Repair 1
2 Monitor 2 HP 1 2 Repair 3
3 ViewSonic 2
I need to count how many printers, Monitors etc. are in Item and how many are under repair
eg.
there are 2 printers in item, and 1 under repair
output should be:
Description count under repair
Printer 2 1
Monitor 1 1
Im using vb.net 2010 and sql server 2008 r2
as much as possible not using stored proc
this is the code im using, i dont know how to get the items under repair, if i try to retrieve it
it cause an error,
SELECT DISTINCT([Description].[Dgroup]) AS Description, COUNT([Item].[DeID]) AS TotalCount " & _
"FROM [Item] JOIN [Description] ON [Description].[DeID] = [Item].[DeID] " & _
"WHERE [Item].[Deleted] IS NULL AND [Item].[Status] IS NULL AND [Item].[DeID] = [Description].[DeID] GROUP BY [Description].[Dgroup] ", Con
something like the transaction is invalid in group by clause, since i think there's no relationship between the description and transaction
can anyone pls guide me how to do it
Thanks for your help
Lethugs
8
Posting Whiz in Training
Recommended Answers
Jump to PostExport a script for your database please.
In VS2010, go to server explorer, right-click the server and select publish from context menuSave, zip, and attach those scripts - make sure you script all objects
Jump to Postno attachment?
Jump to PostYou need sub-queries, one for the totalcount and another for the repair count.
I'm working out the statements now, but I'm rusty... :)
Jump to PostAlmost got it, but returning count of total items under repair for each item... :(
Maybe a UNION SELECT is the way to go - but I'll have to look at it again tomorrow (UTC+5)SELECT COUNT(Item.SID) as totalcount, Dgroup as description, ( SELECT --DGroup, COUNT([Transaction Details].Ttype) …
All 13 Replies
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.