Multiple GROUP BY with certain conditions

Question

Here's a sample of the data set I'm working with:

+------------+----------+--------+
| vegetables | basket   | amount |
+------------+----------+--------+
| tomatoes   | basketA  |   11   |
| cabbage    | basketB  |   21   |
| carrot     | basketC  |   12   |
| beans      | basketD  |   23   |
| cabbage    | basketD  |   13   |
| tomatoes   | basketB  |   23   |  
| beans      | basketB  |   13   |
| carrot     | basketA  |   13   |
+------------+----------+--------+

I have a problem in making a query from the data so that it can display the table as follows. maybe use GROUP BY but i'm having trouble compiling it

+------------+----------+---------+----------+---------+---------+
| vegetables | basketA  | basketB | basketC  | basketD | total   |
+------------+----------+---------+----------+---------+---------+
| tomatoes   |    11    |   23    |          |         |   34    |
| cabbage    |          |   21    |          |   13    |   34    |
| carrot     |    13    |         |    12    |         |   35    |
| beans      |          |   13    |          |   23    |   36    |
+------------+----------+---------+----------+---------+---------+

I'm assuming the baskets are dynamic and you don't really know then beforehand? Also show us some code with any attempt so far. — apokryfos, Aug 31 '21 at 05:34
the type of vegetables and the number of baskets can be dynamic — priyo, Aug 31 '21 at 06:56
This question was originally tagged php and mysqli but there was no content in it to indicate that you have any code using those technologies so it has been retagged as pure MySQL (which we don't even know if it's the correct DBMS used). If you want to get good answers you need to provide more details on what you are using as well as show us some code with your attempt. — apokryfos, Sep 02 '21 at 08:30

sbrbot · Accepted Answer · 2021-08-31T06:20:47.983

This is classic case of pivoting:

SELECT vegetables,
  SUM (CASE WHEN basket = 'basketA' THEN amount ELSE 0 END) AS "basketA",
  SUM (CASE WHEN basket = 'basketB' THEN amount ELSE 0 END) AS "basketB",
  SUM (CASE WHEN basket = 'basketC' THEN amount ELSE 0 END) AS "basketC",
  SUM (CASE WHEN basket = 'basketD' THEN amount ELSE 0 END) AS "basketD",
  SUM (amount) AS "total"
FROM your_table
GROUP BY vegetables

Multiple GROUP BY with certain conditions

1 Answers1