soooo close yet so far 1

[newmediaguy]

Hi Guys

I have written a query that almost does what I need it to do.

I am trying to count the number of products purchased by category.

What I am getting is:

Cat QTY CATID
--------------------------------
Fans 12 AJ
CPU's 35 DF
HDD's 98 GH
Fans 01 AJ

What I need is the combind totals by category. Here is the SQL im using, maybe im just looking at it the wrong way...

SELECT DISTINCT dbo.SearchableFields.OurCategoryDescription, SUM(dbo.OrderBody.Quantity) AS Expr1, dbo.ProductTable.CatID
FROM         dbo.OrderBody INNER JOIN
                      dbo.ProductTable ON dbo.OrderBody.ProductID = dbo.ProductTable.TPID INNER JOIN
                      dbo.SearchableFields ON dbo.ProductTable.CatID COLLATE Latin1_General_CI_AS = dbo.SearchableFields.CatID
GROUP BY dbo.SearchableFields.OurCategoryDescription, dbo.OrderBody.Quantity, dbo.ProductTable.CatID

Any thoughts very appreciated.

Thanks

Glen

 

[dbomrrsm]

Try

SELECT Cat,SUM(QTY)
FROM MYTABLE
GROUP BY Cat

DBomrrsm

 

[newmediaguy]

Thanks DBomrrsm

Was a little more complicated than that but you were spot on thank you VERY much.

Your help is appreciated.

Many thanks

Glen