Hi,
I have 3 tables Description, Item, Transaction
Description Item Transaction
DeID Name IID Name DeID TranNo Type IID Date
1 Printer 1 Styl T10 1 1 Repair 1
2 Monitor 2 MPS 1 2 Repair 3
3 ImpSonic 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 Posti think there's no relationship between the description and transaction
IID is in both tables. That is the link.
(The table setup looks a little strange to me.)
Jump to PostIt doens't have to complicated with joins or subqueries:
SELECT Description.Dgroup AS Description, COUNT(Item.IID) AS TotalCount, sum(case when [Transaction Details].Ttype = 'Repair' then 1 else 0 end) as 'Under Repair' FROM [Transaction Details] RIGHT OUTER JOIN Item ON [Transaction Details].IID = Item.IID LEFT OUTER JOIN Description ON …
Jump to PostItems have multiple entries in [Transaction Details]. That is throwing off the totals. Especially the Ttype = NULL rows. You might want to rethink your database design - especially if you can't query from it... :)
Try this and see if it works for you.
SELECT Description.Dgroup …
All 10 Replies
pritaeas
2,194
¯\_(ツ)_/¯
Moderator
Featured Poster
john.knapp
25
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
adam_k
239
Master Poster
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
john.knapp
25
Posting Whiz in Training
Lethugs
8
Posting Whiz in Training
Lethugs
8
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.