Count different data from one SQL query

Question

I want to get the same results in SQL 2 as in SQL 1 but somehow that SQL query only count the total amount of data for every date, based on the WHERE.

SQL 1 (displays the right amount of data for each day)

SELECT DATE(datetime_logged) AS date,
COUNT(data_status) AS status_a

FROM activity
WHERE id_user = '1'
AND DATE(datetime_logged) != CURDATE()
AND data_status = 'online'
GROUP BY DATE(datetime_logged)
ORDER BY DATE(datetime_logged) DESC
LIMIT 40

SQL 2 (displays the wrong amount of data for each day)

SELECT DATE(datetime_logged) AS date,
(SELECT COUNT(data_status) 
         FROM activity 
         WHERE id_user = '1' 
         AND DATE(datetime_logged) != CURDATE() 
         AND data_status = 'online') AS status_a

FROM activity
GROUP BY DATE(datetime_logged)
ORDER BY DATE(datetime_logged) DESC
LIMIT 40

The table

CREATE TABLE IF NOT EXISTS `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_channel` varchar(50) NOT NULL,
  `id_game` int(11) NOT NULL,
  `data_muted_server` tinyint(4) NOT NULL,
  `data_muted_self` tinyint(4) NOT NULL,
  `data_deafen_server` tinyint(4) NOT NULL,
  `data_deafen_self` tinyint(4) NOT NULL,
  `data_suppressed` tinyint(4) NOT NULL,
  `data_status` varchar(10) NOT NULL,
  `data_game` text,
  `datetime_logged` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `index_datelog` (`datetime_logged`)
)

How can I accomplish this?

Dave C · Accepted Answer · 2016-09-02 16:51:48Z

1

If I understand you correctly, I think you just need to tie the correlated subquery to the main query with:

AND a.datetime_logged=sub.datetime_logged

Example:

SELECT DATE(a.datetime_logged) AS date,
(SELECT COUNT(sub.data_status) 
         FROM activity sub
         WHERE sub.id_user = '1' 
         AND DATE(sub.datetime_logged) != CURDATE() 
         AND sub.data_status = 'online'
         AND a.datetime_logged=sub.datetime_logged) AS status_a

FROM activity a
GROUP BY DATE(a.datetime_logged)
ORDER BY DATE(a.datetime_logged) DESC
LIMIT 40

answered Sep 2, 2016 at 16:51

Dave C

7,4821 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Airikr Over a year ago

Your solution worked as I wanted but the SQL query slows down the website extremely much! From 0-1.5 seconds to easily over 10 seconds.

Dave C Over a year ago

Probably an indexing issue on the date field. I'm not much help on mySQL unfortunately. Your first query was the better approach in my opinion anyway! Why not just use that?

Airikr Over a year ago

Ok. I did use my approach except I added AND DATE(a.datetime_logged) = DATE(sub.datetime_logged) and fixed so I could use that functionality.

Dave C Over a year ago

Glad you got it sorted out! The first query you wrote is definitely the faster approach as it doesn't have to compare the activity table to itself for a count.

Airikr Over a year ago

Yes, but the new SQL query (the one you provided) slows down the website very much. How can I optimize it?

|

Collectives™ on Stack Overflow

Count different data from one SQL query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related