how to count same group value in each date

Question

i have this

table_user

id | name
1 john
2 smith
3 dalton

table_order

id | dates | user_id
x 2017-01-01 1
x 2017-01-01 1
x 2017-01-01 2
x 2017-01-02 1
x 2017-01-02 3
x 2017-01-02 3

i want this result using pure mysql

dates | john | smith | dalton
2017-01-01 | 2 | 1 | 0
2017-01-02 | 1 | 0 | 2

what i can do is only this select a.dates, b.name, count(*) as counts from table_orders a left join table_user b on a.user_id=b.id group by a.dates, b.name

result:

dates | name | counts
2017-01-01 john 2
2017-01-01 smith 1
2017-01-02 john 1
2017-01-02 dalton 2

then i process using php.

so, how to do this using pure mysql? thanks

you should use Pivot https://stackoverflow.com/questions/7674786/mysql-pivot-table — Saeed Taran, Oct 31 '17 at 08:09
ill try to edit my question since pivot table cant work dinamycally — plonknimbuzz, Oct 31 '17 at 08:16

score 1 · Accepted Answer · answered Oct 31 '17 at 08:17

1

You can do it with dynamic sql query.

Query

set @query = null;
select
  group_concat(distinct
    concat(
      'coalesce(sum(case when `name` = ''',
      `name`, ''' then 1 end), 0) as `', `name` , '`'
    )
  ) into @query
from `your_table_name`;

set @query = concat('select `dates`, ', @query, ' from `your_table_name` 
              group by `dates`
');

prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

`Find a demo here`

answered Oct 31 '17 at 08:17

Ullas

11,450
4
33
50

wtf. awesome bro. thanks – plonknimbuzz Oct 31 '17 at 08:24
@plonknimbuzz : Please mark it as answer if it solved your problem – Ullas Oct 31 '17 at 08:31
ofc i will. i just busy to explore your code XD .sry – plonknimbuzz Oct 31 '17 at 09:58

how to count same group value in each date

1 Answers1

Find a demo here

`Find a demo here`