Actual Table
| User | Product | Date |
|---|---|---|
| User A | Product 1 | 18-01-2022 |
| User A | Product 2 | 17-01-2022 |
| User B | Product 2 | 13-01-2022 |
| User A | Product 2 | 12-01-2022 |
| User A | Product 2 | 18-01-2022 |
| User B | Product 1 | 01-01-2022 |
| User B | Product 2 | 04-01-2022 |
| User A | Product 1 | 05-01-2022 |
| User B | Product 3 | 05-01-2022 |
What should be the query to generate the following output?
Output
| User | Count of Product 1 | Count of Product 2 | Count of Product 3 | Total |
|---|---|---|---|---|
| User A | 2 | 3 | 0 | 5 |
| User B | 1 | 2 | 1 | 4 |
CodePudding user response：
Use conditional aggregation:
SELECT User, SUM(Product = 'Product 1') AS Count_Product_1,
SUM(Product = 'Product 2') AS Count_Product_2,
SUM(Product = 'Product 3') AS Count_Product_3,
COUNT(*) AS Count_All_Products
FROM yourTable
GROUP BY User;