asked on

Sql query - sum up things

Hello all,
I have this query =>
SELECT
Product.ProductID AS [Product ProductID]
,Product.CatalogNum
,Product.ProductName
,Customer.CustomerName
,[Order].Quantity
,[Order].Refund
,[Order].Price
,[Order].ProductID AS [Order ProductID]
,Product.DisplayOnReport
FROM
[Order]
INNER JOIN Customer
ON Customer.CustomerID = [Order].CustomerID
INNER JOIN Product
ON Product.ProductID = [Order].ProductID
Where Customer.CustomerID = @CI ORDER BY Product.CatalogNum

The same customer probably ordered the same product more than once, I'd like to get 1 row per product (1 row that sums up all the Quantity from other rows).
I need that for a report I'm building.

thank you

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kulpem

ASKER

That worled for me

SELECT
  Product.ProductID AS [Product ProductID]
  ,Product.CatalogNum
  ,Product.ProductName
  ,Customer.CustomerName -- one customer, works if all the same customer
  ,SumQty=Sum([Order].Quantity)
,SumRef=Sum([Order].Refund)
,[Order].Price

  ,Product.DisplayOnReport
FROM
  [Order]
  INNER JOIN Customer
    ON Customer.CustomerID = [Order].CustomerID
  INNER JOIN Product
    ON Product.ProductID = [Order].ProductID
    Where Customer.CustomerID = 319
--ORDER BY Product.CatalogNum
GROUP BY Product.ProductID, Product.CatalogNum, Product.ProductName, Product.DisplayOnReport,
Customer.CustomerName, [Order].Price

Open in new window

kulpem

ASKER

thank you

kulpem

ASKER

Well it not working that good I forgot the mention the dates
I need something like that =>
using Date range. but this query outs me back to square 1, I get each order in one row, instead of summing up Quntity and Refund

SELECT
  Product.ProductID AS [Product ProductID]
  ,Product.CatalogNum
  ,Product.ProductName
  ,Customer.CustomerName 
  ,SumQty=Sum([Order].Quantity)
  ,SumRef=Sum([Order].Refund)
  ,[Order].Price
  ,[Order].Date
  ,Product.DisplayOnReport
FROM
  [Order]
  INNER JOIN Customer
    ON Customer.CustomerID = [Order].CustomerID
  INNER JOIN Product
    ON Product.ProductID = [Order].ProductID
    Where Customer.CustomerID = 319 AND [Order].Date BETWEEN '01-01-2000' AND '01-01-2030'
	GROUP BY Product.ProductID, Product.CatalogNum, Product.ProductName, Product.DisplayOnReport,
	Customer.CustomerName, [Order].Price, [Order].Date

Open in new window

kulpem

ASKER

Sorry my mistake.
I better goto sleep soon.
verything is cool I got it.