Query for getting the sum of all unique entries in a column

Question

Currently I'm using mysql to return the sum of all data in a column that have the same word. To do this I'm using the following query:

SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, 
SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, 
SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, 
SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, 
SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted, 
SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold, 
SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let 
FROM `crm_logs`

This gives out the correct output in my database for all the terms I've specified, but now I've found that there are more status variables in the database other than these specified above. So I want a way to have the same functionality of this statement, but make the status variable dynamic.

Basically if this is the query, SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, both the occurrences of draft should be dynamic.

Answer 1

There's a far easier way to do what you want - I was thinking about this last night - see my second fiddle here.

You can do something like the following (all the code below is available on the fiddle here):

CREATE TABLE test
(
  t_id INTEGER NOT NULL PRIMARY KEY,
  status_to VARCHAR (50)
);

Then populate it:

INSERT INTO test VALUES
(7, NULL), (8, 'Test_val_1'), (9, 'Test_val_2'), 
 (3, 'Publish'), (4,  'Action'),  (5, 'Sold'),  (6, 'Let'), (10, 'Draft'), (11, 'Unpublish'), 
(12, 'Publish'), (13, 'Action'), (14, 'Sold'), (15, 'Let'), (26, 'Draft'), (16, 'Unpublish'),
(17, 'Publish'), (18, 'Action'), (19, 'Sold'), (20, 'Let'), (27, 'Draft'), (21, 'Unpublish'),
(22, 'Publish'), (23, 'Action'), (24, 'Sold'), (25, 'Let'), (1,  'Draft'),  (2, 'Unpublish'),
(29, NULL), (30, NULL), (35, 'Test_val_1'), (31, 'Test_val_2'),
(45, 'Single_1'), (46, 'Single_2');

Note that I have added 2 singleton values (Single_1 and Single_2) to examine how one would, for example, count the DISTINCT values which had greater than 1 record in your table... it's much more difficult with "classic" SQL - with window functions, it's quite easy.

The new "simple" query is:

SELECT
  COUNT(DISTINCT(status_to)) AS "Unique count"
FROM
  test;

(New) result:

Unique count
          10

Notice the extreme difference in the results of EXPLAIN ANALYZE at the bottom of the fiddle for this new query and my old one!

This query demonstrates how you can easily pick out those DISINCT values of status_to that have more than one record:

SELECT COUNT(rn) OVER (PARTITION BY rn) AS "Unique count"
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
WHERE rn > 1
GROUP BY status_to
ORDER BY status_to
LIMIT 1;

Result:

Unique count
           8

8 = 10 (total) minus the 2 singletons!

I also included this SQL - which lets you easily count (manually) the number of UNIQUE entries.

SELECT status_to, MAX(rn)
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
WHERE rn > 1        <<==== ****
GROUP BY status_to
ORDER BY status_to;

Result:

status_to    MAX(rn)
  Action          4
  Draft           4
  Let             4
  Publish         4
  Sold            4
  Test_val_1      2
  Test_val_2      2
  Unpublish       4
8 rows              <<---- note 8 rows!

I've left a few more snippets in the second fiddle - if you really want to learn about SQL, I would invite you to look at both the old and the new and also to try running the code using PostgreSQL - it's interesting!

Finally, if you wish to dynamically construct a row (as per your original question) with all of your DISTINCT values and their COUNT()s, then you could possibly use some form of RECURSIVE CTE - but that's for another question!

Answer 2

Write a stored procedure that constructs the query using CONCAT() and executes it via PREPARE and EXECUTE.

Or construct the query in your application language using its string functions.

As for the discussion in the wiki answer, I add this

COALESCE(SUM(status_to = 'Draft'), 0)

But that is only necessary if status_to can be NULL. Otherwise, this works:

SUM(status_to = 'Draft')

Answer 3

jkavalik: You should instead do:

select status_to, count(1) 
from crm_logs 
group by status_to

It has data in rows instead of columns but that's a detail. If you really need to, it can be solved with dynamic SQL too, but the group by is still better as a base query.

---

Akina: In MySQL you may use not SUM(CASE WHEN status_to = 'Draft' THEN 1 END) but more simple (and clear) SUM(status_to = 'Draft').

Additionally, this expression will produce zero instead of NULL if no rows matched.

count(status_to = 'Draft') would count all not null values regardless of the value. SUM(status_to = 'Draft') will produce null only when no non-null status_to value is present.

Lennart: count(nullif(status_to = 'Draft',0)) would handle both situations.