CODEWITHSUNDEEP
mysqljoincountsubquery
peter
peter

Reputation: 35

Count with join, subquery and group by

I have this table:

CREATE TABLE `logs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `visitor_id` INT(11) NOT NULL,
    `date_time` DATETIME NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `id_no` INT(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `info`
 VALUES 
 (1,20, 'vip'),(2,21, 'customer'),(3,22,'vip')
,(4,23, 'customer'),(5,24, 'vip'),(6,30,'customer')
,(7,31, 'vip'),(8,32,'customer'),(9,33,'vip' ),(10,34, 'vip'),(11,35,'vip');

INSERT INTO `logs`
 VALUES 
 (1,20, '2019-01-01 08:00:00'),(2,21, '2019-01-01 08:05:00'),(3,22,'2019-01-01 08:08:00')
,(4,23, '2019-01-01 08:10:00'),(5,24, '2019-01-01 08:15:00'),(6,30,'2019-01-02 09:00:00')
,(7,31, '2019-01-02 09:10:00'),(8,32,'2019-01-02 09:15:00'),(9,33,'2019-01-02 09:17:00' ),(10,34, '2019-01-02 09:18:00');

This query:

select date(l.date_time) as `date`, (select count(distinct(l.visitor_id)) from `logs` l join info i on (i.id_no = l.visitor_id) where i.`name` = 'CUSTOMER' and l.visitor_id=i.id_no) as total_customer, (select count(l.visitor_id) from `logs` l join info i on (i.id_no = l.visitor_id) where i.`name` = 'vip') as total_vip, count(distinct(l.visitor_id)) as total from `logs` l  join info i on (i.id_no = l.visitor_id) where l.date_time between '2019-01-01 00:00:00' and '2019-01-02 23:00:00' group by date(l.date_time);

has this result:

  | date       | total_customer | total_vip |  total   |
  -------------------------------------------------------
  | 2019-01-01 |       4        |     6     |    5     |      
  | 2019-01-02 |       4        |     6     |    5     |

my desired result is this:

  | date       | total_customer | total_vip |  total   |
  -------------------------------------------------------
  | 2019-01-01 |       2        |     3     |    5     |      
  | 2019-01-02 |       2        |     3     |    5     |

May I know what's wrong with my query? I'm using mysql 5.5. Thank you.

Upvotes: 1

Views: 39

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

You don't need subqueries, you can use sum() case

select date(l.date_time) as date
    , sum(case when i.name = 'customer' then 1 else 0 end) as customers
    , sum(case when i.name = 'vip' then 1 else 0 end) as visitors
    , count(1) as total
from logs l 
join info i on (i.id_no = l.visitor_id) 
where l.date_time between '2019-01-01 00:00:00' and '2019-01-02 23:00:00' 
group by date(l.date_time);

Upvotes: 1

Related Questions