HELP PLLSSSSS

[Lethugs]

     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

[pritaeas]

i 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.)

[john.knapp]

[Transaction Details] is the table he wants to pull from anyway, not [Transactions].
I almost have the data, but having trouble with the outer joins/subquery select. (a bit over my head, it's been years since I did any complex queries.

Here's what I have so far:

SELECT     Description.Dgroup AS Description, COUNT(Item.IID) AS TotalCount, [Transaction Details].Ttype
FROM         [Transaction Details] RIGHT OUTER JOIN
                      Item ON [Transaction Details].IID = Item.IID LEFT OUTER JOIN
                      Description ON Item.DeID = Description.DeID
WHERE     (Item.Status IS NULL)
GROUP BY Description.Dgroup, [Transaction Details].Ttype

That query pulls the following:

Description TotalCount  CurrentStatus
Monitor 1   NULL
Object Oriented Programming 1   NULL
Printer 2   NULL
Tower   1   NULL
Printer 1   ItemTransfer
Monitor 1   Releasing
Network Switch  1   Releasing
Printer 1   Releasing
Tower   2   Releasing
USB 1   Releasing
Monitor 1   Repair
Network Switch  1   Repair
Printer 2   Repair

I'm stuck trying to get the repair count from that, while keeping the totalcount

[john.knapp]

Let me be clear.
By stuck, I mean that with my current level of knowledge I am unable to solve the question.
If anyone in the MSSQL forum would like to jump in, please feel free to do so... :)
Thanks

[adam_k]

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 = Description.DeID
WHERE     (Item.Status IS NULL)
GROUP BY Description.Dgroup

But I don't see transaction details as a table in the original post and the [Description].[Dgroup] that OP is trying to select doesn't appear in table specs.

[john.knapp]

You're right - the OP didn't list [Transaction Details]... I had him script the db though, and that table is where the repair status is listed.

Just ran the query and got the requested results. Awesome!
Thanks for your help - I knew SUM() needed to be in there somewhere, but I was missing the CASE statement, which obviously gave me bad results.

Thanks Adam!

[Lethugs]

Thank you for helping me out Sir Adam, I will try this query later.i will update this thread after. :)

[Lethugs]

there's a slight problem now, the count of item under repair is ok. But the total count is giving wrong count. It counts the item in Item Table and Transaction details table.

eg. If total monitor in [Item] table is 2, and one of which is under repair, the total count of monitor now in [Item] table is 3, which should be 2 only.

Where should i modify it?
thanks again

[john.knapp]

Items 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 AS Description,
        COUNT(Item.IID) As TotalCount,
        [Under Repair]
FROM [Description] JOIN [Item]
    ON Description.DeID = Item.DeID
    JOIN (SELECT Description.Dgroup AS Description, 
                    SUM(CASE WHEN 
                        [Transaction Details].Ttype = 'Repair' 
                        THEN 1 ELSE 0 END) AS 'Under Repair'
            FROM [Transaction Details]
                JOIN [Item] ON [Transaction Details].[IID] = [Item].[IID]
                JOIN [Description] ON Description.DeID = Item.DeID
            GROUP BY DGroup) AS tmp
    ON Description.Dgroup = tmp.Description
WHERE [Under Repair] IS Not Null
GROUP BY DGroup, [Under Repair]

[Lethugs]

I know im having a lot of problem in my database design but that's what my company is requiring me, and i just can't modify it now, there will be a lot of codes and tables that will be affected. I'm just doing what i can to finished this project with your help

BTW I will try the code, I will post the update. Thanks again :)

[Lethugs]

The code WORKS!! I just made some modification as what the the company requires. I have idea now how to do the other codes. Million thanks to adam_k and john.knapp.