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    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

[john.knapp]

Export a script for your database please.
In VS2010, go to server explorer, right-click the server and select publish from context menu

Save, zip, and attach those scripts - make sure you script all objects

[Lethugs]

hi, attached is the requested script. thanks for considering my post.

[john.knapp]

no attachment?

[Lethugs]

sorry there was an error

[john.knapp]

You need sub-queries, one for the totalcount and another for the repair count.
I'm working out the statements now, but I'm rusty... :)

[Lethugs]

I tried using union statements also but generated on error about the grouping clause, since the transaction has no relationship with the description, could you show some example on your suggestion pls.

the connection im seeing to accomplish it is from description to item, then item to transaction but i dont know how to do it

[john.knapp]

Almost 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)
    FROM [Transaction Details]
      INNER JOIN [Item] ON 
        [Transaction Details].IID = Item.IID
      INNER JOIN dbo.Description ON
        [Description].DeID = [Item].DeID
     WHERE [Transaction Details].TType = 'repair'
     --GROUP BY DGroup
 ) As [Under Repair]
FROM dbo.Description INNER JOIN
    dbo.Item ON dbo.Item.DeID = dbo.Description.DeID
WHERE [Item].Status IS NULL
GROUP BY DGroup

[Lethugs]

thank you so much for helping, im really stuck here.
God bless :)

[john.knapp]

See your cross-post in the MSSQL forum for the correct query.

Generally speaking, cross-posting is bad etiquette however, and I should have had you move that post to the MSSQL forum at the beginning of the thread. Regardless, adam_k has solved your question.
Please upvote his answer and mark both threads as solved.
Thanks

[Lethugs]

sorry, i just dont know where i can post it. it is both under vb.net and mssql. anyway thanks for helping, i will check the code. :)

[john.knapp]

No problem. The query he gave you works, I checked it as soon as it was posted.

[Lethugs]

There's a little problem with the query, i posted it in my croos post, hope you can still help me. thanks again

[john.knapp]

I posted the updated query in the other thread. Please close this thread, as I am still watching the other.