Erik Edgren Erik Edgren - 3 months ago 9
MySQL Question

Count different data from one SQL query

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


result-1

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


result-2

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?

Answer

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