Member Avatar for Lethugs 
Hi, I have 3 tables
        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
Thanks for your help
  • 2 Contributors
  • 1 Reply
  • 304 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by darkagn
Member Avatar for darkagn

Something like the following query should get you close:

-- need a count of the objects by description and transaction
SELECT Description.Name, COUNT(Item.Name) AS count, COUNT(Transaction.Type) AS [under repair]
FROM Description
-- describe the relationship between the tables by joining
INNER JOIN Item ON Description.DeID = Item.DeID
INNER JOIN Transaction ON Item.IID = Transaction.IID
-- only interested in repair transaction type
WHERE Transaction.Type = 'Repair'
-- because we use aggregate functions, need to describe how to group the aggregations (counts)
GROUP BY Description.Name

Have a play with that query, it may need to be adjusted to get the right results but should give you a start.

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.