How to do grouping by multiple columns in MySQL Select Statement-CodePudding

Above is the output from my Select Statement in MySQL, can anyone please help me so that I can group above result by exp_date and username. So it will be only 1 row for any username for the same date.

It should show lodging, boarding, laundry, and conveyance column's value in a single row of 2021-10-26 for username S.M.gadekar.

Tried all the ideas but I am not getting the exact result. What is the actual optimized solution for this?

CodePudding user response：

You select the columns you want out of your group, and use some kind of aggregate and alias for the rest - since it looks like all expense values for one date and name are set in only one row, MAX() could work, but SUM() is the safer choice for aggregation here. To group by multiple columns, just have a comma separated list of columns in your GROUP BY statement:

SELECT username, exp_date, 
    SUM(lodging) AS lodging,
    SUM(boarding) AS boarding,
    SUM(laundry) AS laundry,
    SUM(conveyance) AS conveyance
FROM table
GROUP BY exp_date, username;

That said the table should probably be normalized into three tables, since this looks like it's only in 2nd normal form - but the design works well enough for the purpose.

sqlFiddle

If you specifically want a row for one person on one day, not a row for each person each day there is data, your where clause goes between your from and group clauses:

sqlFiddle

CodePudding user response：

more than column group by separated by , GROUP BY exp_date , username;