how to group by two column in sql?

Question

I have two tables one is "events" and second is "tickets":

Events table:

============================
| event_id |   event_name  |
============================
|   101    | Running Event |
|   102    | Cycling Event |
============================

Tickets table:

==============================================================
| ticket_id | event_id | ticket_number | ticket_generate_date|
==============================================================
|    1      |    101   |    40001      |  2018-08-21 17:05   |
|    2      |    101   |    40002      |  2018-08-21 18:05   |
|    3      |    102   |    40001      |  2018-08-21 19:05   |
|    4      |    101   |    40003      |  2018-08-23 20:05   |
|    5      |    101   |    40004      |  2018-08-24 20:05   |
==============================================================

Now i want run query so that my output comes like below:

================================================
| count ticket |   event_name  | day wise data |
================================================
|       2      | Running Event |  2018-08-21   |
|       1      | Cycling Event |  2018-08-21   |
|       1      | Running Event |  2018-08-23   |
|       1      | Running Event |  2018-08-24   |
================================================

I have tried the following query:

SELECT COUNT(ticket_id), ticket_generate_date FROM Tickets 
WHERE ticket_generate_date >= DATE_FORMAT(curdate(), '%Y-%m-01') 
GROUP BY DATE_FORMAT(ticket_generate_date, '%d-%b');

@Rafael i have tried this query SELECT COUNT(ticket_id), ticket_generate_date FROM Tickets WHERE `ticket_generate_date` >= DATE_FORMAT(curdate(), '%Y-%m-01') GROUP BY DATE_FORMAT(`ticket_generate_date`, '%d-%b') — Santosh Khatri, Aug 22 '18 at 09:50
Santosh, add this to the main task so everybody can read it, please. :) — Rafael, Aug 22 '18 at 09:50
Possible duplicate of [Using group by on multiple columns](https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) — Govind Samrow, Aug 22 '18 at 10:14

Rafael · Accepted Answer · 2018-08-22T10:24:55.393

You just need to add the second group option to the option you already have with a comma.

I also used aliases and INNER JOIN to get the event name.

SELECT 
    COUNT(t.ticket_id),
    e.event_name,
    DATE_FORMAT(t.ticket_generate_date, '%Y-%m-%d') as day_wise
FROM 
    Tickets t
INNER JOIN
    Events e ON e.event_id = t.event_id
WHERE 
    t.ticket_generate_date >= DATE_FORMAT(curdate(), '%Y-%m-01') 
GROUP BY 
    DATE_FORMAT(t.ticket_generate_date, '%Y-%m-%d'), e.event_name

It's working here: http://sqlfiddle.com/#!9/b235c/1/0

score 1 · Answer 2 · answered Aug 22 '18 at 10:12

If you include an additional column name in the group by clause - in this case I chose event_name the final output is more or less as expected. To output all the details requested though you would need to join the two tables ( or use a nested select )

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| event_id   | int(11)     | NO   |     | NULL    |       |
| event_name | varchar(50) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+



+----------------------+-----------+------+-----+-------------------+----------------+
| Field                | Type      | Null | Key | Default           | Extra          |
+----------------------+-----------+------+-----+-------------------+----------------+
| id                   | int(11)   | NO   | PRI | NULL              | auto_increment |
| event_id             | int(11)   | NO   |     | 0                 |                |
| ticket_number        | int(11)   | NO   |     | 0                 |                |
| ticket_generate_date | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
+----------------------+-----------+------+-----+-------------------+----------------+



select 
    count( t.id ) as 'sales',
    e.`event_name` as `event`,
    date_format( t.`ticket_generate_date`, '%y-%m-%d' ) as `day wise data`
from `tickets` t
    join `events` e on e.`event_id`=t.`event_id`
where t.`ticket_generate_date` >= date_format( curdate(), '%y-%m-01' )
group by e.event_name, date_format( t.`ticket_generate_date`, '%d-%b' );


+-------+---------------+---------------+
| sales | event         | day wise data |
+-------+---------------+---------------+
|     1 | Cycling event | 18-08-21      |
|     2 | Running Event | 18-08-21      |
|     1 | Running Event | 18-08-23      |
|     1 | Running Event | 18-08-24      |
+-------+---------------+---------------+

or, to further refine the result you can order by date

select 
    count( t.id ) as 'sales',
    e.`event_name` as `event`,
    date_format( t.`ticket_generate_date`, '%y-%m-%d' ) as `day wise data`
from `tickets` t
    join `events` e on e.`event_id`=t.`event_id`
where t.`ticket_generate_date` >= date_format( curdate(), '%y-%m-01' )
group by e.event_name, date_format( t.`ticket_generate_date`, '%d-%b' )
order by `day wise data`;

+-------+---------------+---------------+
| sales | event         | day wise data |
+-------+---------------+---------------+
|     2 | Running Event | 18-08-21      |
|     1 | Cycling event | 18-08-21      |
|     1 | Running Event | 18-08-23      |
|     1 | Running Event | 18-08-24      |
+-------+---------------+---------------+

score 0 · Answer 3 · answered Aug 22 '18 at 10:23

0

Try this:

select sum(ticket)id) ticket_count
       (select event_name from Events where event_id = t.event_id) event_name,
       cast(ticket_generate_date as date)
from Tickets t
group by event_id, cast(ticket_generate_date as date)

answered Aug 22 '18 at 10:23

Michał Turczyn

32,028
14
47
69

how to group by two column in sql?

3 Answers3