Erik Edgren Erik Edgren - 4 months ago 9
SQL Question

Merge the rows and give me the total count

I want to count how many unique games a user have been played. Here's how the current SQL query looks like:

SELECT COUNT(DISTINCT d.id_game) AS c
FROM discord AS d
JOIN discord_games AS dg
ON d.id_game = dg.id
WHERE d.id_user = '1'
AND d.id_game != '0'
GROUP BY d.id_game
ORDER BY dg.data_name ASC


And here's now the database looks like:

CREATE TABLE IF NOT EXISTS `discord` (
`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,
`datetime_logged` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)


CREATE TABLE IF NOT EXISTS `discord_games` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)


CREATE TABLE IF NOT EXISTS `discord_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_id` text NOT NULL,
`data_name` varchar(50) NOT NULL,
`data_avatar` text,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
)


The SQL query counts correctly but it only counts 1 per row.

Example of how I'm thinking. If the user with the ID 1 have played the same game 24 times (for an example Battlefield 3) it will return 1 played game. If the same user plays another game 37 times, it will return 2 played games and so on.

SUM(DISTINCT ...
doesn't work since it only replace 1 with the game ID.

How can I sum the rows so it displays the number 2 instead of 2 rows?

Answer

You could just remove the group by property and count the distinct games played. It will return the number of distinct gamesp layed by the user 1

SELECT COUNT(DISTINCT d.id_game) AS c
FROM discord AS d
JOIN discord_games AS dg
ON d.id_game = dg.id
WHERE d.id_user = '1'
AND d.id_game != '0'
ORDER BY dg.data_name ASC