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
Jump to Post
It 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 Item.DeID = …
All 10 Replies
Be a part of the DaniWeb community
We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.