-1

enter image description here

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?

Rick James
  • 135,179
  • 13
  • 127
  • 222
idleMind
  • 131
  • 5
  • 16
  • 1
    `GROUP BY username, exp_date` – Barmar Oct 26 '21 at 16:57
  • See https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html for the functions you can use to aggregate the other columns in each group. – Barmar Oct 26 '21 at 16:59
  • 1
    Does this answer your question? [Is it possible to GROUP BY multiple columns using MySQL?](https://stackoverflow.com/questions/1841426/is-it-possible-to-group-by-multiple-columns-using-mysql) – Andrew Oct 26 '21 at 17:30
  • If you can use `MAX()` or `SUM()`, see Andrew's answer, else see the `[groupwise-maximum]` tag. – Rick James Oct 26 '21 at 20:07
  • @RickJames you can link tags in comments with `[tag:]` syntax like [tag:groupwise-maximum] – Andrew Oct 27 '21 at 15:52

2 Answers2

1

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

Andrew
  • 1,544
  • 1
  • 18
  • 36
0
  • more than column group by separated by , GROUP BY exp_date , username;
Aml
  • 431
  • 3
  • 8