• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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
0
kulpem
Asked:
kulpem
  • 4
1 Solution
 
cyberkiwiCommented:
Hi there,
here you go
SELECT
  Product.ProductID AS [Product ProductID]
  ,Product.CatalogNum
  ,Product.ProductName
  ,Customer.CustomerName -- one customer, works if all the same customer
  ,SumQty=Sum([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
GROUP BY Product.ProductID, Product.CatalogNum, Product.ProductName, Product.DisplayOnReport,
Customer.CustomerName

Open in new window

0
 
kulpemAuthor Commented:
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

0
 
kulpemAuthor Commented:
thank you
0
 
kulpemAuthor Commented:
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

0
 
kulpemAuthor Commented:
Sorry my mistake.
I better goto sleep soon.
verything is cool I got it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now