SQLTeam.com | Weblogs | Forums

SQL Sum by Certain Product


#1

Hello,

I have a Table the contains the following column.

  1. Client Name
  2. Product
  3. Date Purchase
  4. Quantity
  5. Amount.

Is it possible to display the output on this way.

Client Name Date Purchase Quantity of Product A Total Amount of Product A Quantity of Other Product Total Amount of Other Product.

The Idea is to separate the quantity and total amount of a product A from other Product.

Thanks


#2

Please post,table scripts with some sample data.


#3

Sure; not a problem once you've seen the technique used :-).

SELECT
[Client Name],
[Date Purchase], 
SUM(CASE WHEN Product = 'Product A' THEN Quantity ELSE 0 END) AS [Quantity of Product A],
SUM(CASE WHEN Product = 'Product A' THEN Amount ELSE 0 END) AS [Total Amount of Product A],
SUM(CASE WHEN Product = 'Product A' THEN 0 ELSE Quantity END) AS [Quantity of Other Product],
SUM(CASE WHEN Product = 'Product A' THEN 0 ELSE Amount END) AS [Total Amount of Other Product]
FROM a_Table
GROUP BY
[Client Name],
[Date Purchase]