1

SQL Fiddle:

CREATE TABLE Sales (
    Product_ID VARCHAR(255),
    Country VARCHAR(255),
    Sales_Volume VARCHAR(255)
);

INSERT INTO Sales
(Product_ID, Country, Sales_Volume)
VALUES 
("P001", "US", "500"),
("P001", "US", "100"),
("P003", "US", "800"),
("P002", "DE", "300"),
("P001", "DE", "700"),
("P002", "NL", "200"),
("P002", "NL", "400");

In the table I have the the Sales in different Countries.
Now, I want to sum up the Sales per Country.
The expected result should look like this:

                US             DE 
P001           600            700
P002           NULL           300
P003           800            NULL

So far I have the following query:

SELECT
Product_ID,
Country,
SUM(Sales_Volume)
FROM Sales
WHERE 
Country = "US" 
OR Country ="DE"
GROUP BY 1,2;

This query basically works but instead of displaying the Country as column name it displays the country as value.

What do I need to change in my SQL to get the result I need?

Michi
  • 4,465
  • 6
  • 26
  • 64
  • 2
    Consider handling issues of data display in application code. With that in mind, query-wise, I would (pretty much) stick with what you've got – Strawberry Feb 28 '20 at 14:08

2 Answers2

0

You could use conditional aggregation:

SELECT Product_ID,
       SUM(CASE WHEN Country = 'US' THEN Sales_Volume ELSE 0 END) as US,
       SUM(CASE WHEN Country = 'DE' THEN Sales_Volume ELSE 0 END) as DE
FROM Sales
GROUP BY Product_ID;
Strawberry
  • 33,338
  • 13
  • 38
  • 57
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
0

If you only need for 2 countries or less number of countries that you can hard code in your query - 

SELECT
Product_ID,
sum(IF(country='DE',sales_volume,NULL)) 'DE',
sum(IF(country='US',sales_volume,NULL)) 'US'
FROM Sales
Sudipta Mondal
  • 2,510
  • 1
  • 18
  • 20