Need help...asp.net count sql..

[mat daus]

im very new to asp.net.
i develop a website for e-commerce..now i developing a page to display the statistic of the product that bought by costumer..
here is my database structure..
Table Product-ProductID,ProductName,Description
Table Transaction-TransactionID,User,ProductID,Quantity

-in table Transaction, i store the product that bought by user with user name and quantity.
-table product, store the information about product

-this is my data, assume Bag ProductID=1 and Pencil ProductID=2 in table transaction..
-for table transaction:
TransactionID | User | ProductID | Quantity
1 | ali | 1 | 2
1 | abu | 2 | 3
1 | qwe | 1 | 5

i want make a statistic about the product that bought by users..
im using this Select query= "SELECT Product.ProductName, COUNT(Transaction.ProductID) AS ProductCount FROM Product INNER JOIN Transaction ON Product.ProductID = Transaction.ProductID GROUP BY Product.ProductName"

when using this query the result is,
ProductName | ProductCount
Bag | 2
Pencil | 1

i just want my result shown like i stated below..but i dont know how to do it..
ProductName | ProductCount
Bag | 7
Pencil | 3

please help me....

[Pete OHanlon]

Try this query instead:

SELECT DISTINCT Product.ProductName, SUM(Transaction.Quantity) AS ProductCount
FROM Product INNER JOIN Transaction ON Product.ProductID = Transaction.ProductID
GROUP BY Product.ProductName

By using COUNT, you were counting the number of lines that corresponded in the result set, not the total.

Forgive your enemies - it messes with their heads

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

[Dennis E White]

just a note but next time don't format your whole question as code. I completely ignored your request because I didn't want to sort through your comments and your code.

as if the facebook, twitter and message boards weren't enough - blogged