CODEWITHSUNDEEP
mysqlsql
Khairul
Khairul

Reputation: 425

MySQL - Group By WITH ROLLUP Base On Month and User Id

I want to show the data base on month and follow by the total value on that month.
For Example:

|    ID    |           Name          | Amount |   Date   | Month | Day  | Total |
--------------------------------------------------------------------------------
| ZAK00001 | Committee Meeting April |  4000  | 06-02-19 |   2   |  6   |  4000 |
| Total                                                                 |  4000 |
| ZAK00009 | Meeting May             |  500   | 10-06-19 |   6   |  10  |  1000 | 
| ZAK00011 | Meeting May             |  3000  | 20-06-19 |   2   |  20  |  3000 |
| Total                                                                 |  4000 |

How to achieve the above data in SQL? So far, I had tried to use.

Update Query and Result.

I add IFNULL(a.id, 'Total')

Query

SELECT
    -- a.id,
    IFNULL(a.id, 'Total'),
    m.name,
    a.amount,
    date_format( str_to_date( m.meeting_date, "%d/%m/%Y" ), "%d-%m-%y" ) AS date,
    MONTH (
    str_to_date( m.meeting_date, "%d/%m/%Y" )) AS month,
    DAYOFMONTH(
    str_to_date( m.meeting_date, "%d/%m/%Y" )) AS day,
    sum(a.amount)
FROM
    applications a
    INNER JOIN meeting_application ma ON a.id = ma.application_id
    INNER JOIN meetings m ON ma.meeting_id = m.id 
GROUP BY monthName, a.id WITH ROLLUP

Result

|    ID    |           Name          | Amount |   Date   | Month | Day  | Total |
---------------------------------------------------------------------------------
| ZAK00001 | Committee Meeting April |  4000  | 06-02-19 |   2   |  6   | 4000  |
| Total    | Committee Meeting April |  4000  | 06-02-19 |   2   |  6   | 4000  |
| ZAK00009 | Meeting May             |  1500  | 10-06-19 |   6   |  10  | 1500  |
| ZAK00011 | Meeting May             |  500   | 20-06-19 |   6   |  20  | 500   |
| Total    | Meeting May             |  500   | 20-06-19 |   6   |  20  | 2000  |

Can we do it in the query?

I am able to display the Total text. How to not display the repeated values?

Upvotes: 0

Views: 297

Answers (1)

Vivek
Vivek

Reputation: 803

There should be some permanent fix but this will also resolve your issue: Using Case Statement:

SELECT
    -- a.id,
    IFNULL(a.id, 'Total'),
    case when a.id is not null then m.name else null end as name,
    case when a.id is not null then a.amount else null end as amount,
    case when a.id is not null then date_format( str_to_date( m.meeting_date, "%d/%m/%Y" ), "%d-%m-%y" ) else null end AS date,
    case when a.id is not null then MONTH (
    str_to_date( m.meeting_date, "%d/%m/%Y" )) else null end AS month,
    case when a.id is not null then DAYOFMONTH(
    str_to_date( m.meeting_date, "%d/%m/%Y" )) else null end AS day,
    sum(a.amount)
FROM
    applications a
    INNER JOIN meeting_application ma ON a.id = ma.application_id
    INNER JOIN meetings m ON ma.meeting_id = m.id 
GROUP BY monthName, a.id WITH ROLLUP

Upvotes: 1

Related Questions