Patrick Lorio Patrick Lorio - 4 months ago 26
SQL Question

Select count of distinct column in table

Lets say I have a sql table in the format:

id INT PRIMARY KEY AUTO_INCREMENT,
action TINYINT NOT NULL,
user_id INT NOT NULL


I would like to build a query that produces the same result as the following:

SELECT
(SELECT COUNT(*), action FROM table WHERE action=1 AND user_id=1),
(SELECT COUNT(*), action FROM table WHERE action=2 AND user_id=1),
(SELECT COUNT(*), action FROM table WHERE action=3 AND user_id=1),
...
(SELECT COUNT(*), action FROM table WHERE action=n AND user_id=1)


(1<->n is simply the range of
action
)

But without all the redundancy and inefficiency.

Answer

If I understand the question correctly, this is what you're looking for:

SELECT   action, COUNT(*)
FROM     your_table
WHERE    user_id = 1
GROUP BY action