How to find unique value in a MySQL Group By query?

user_3278763 0
Viewed 67

MySQL TABLE : numbers

+----+-----+------------+
| ID | NUM |   CREATED  |
+----+-----+------------+
| 1  | 11  | 2018-01-01 |
+----+-----+------------+
| 2  | 22  | 2018-02-01 |
+----+-----+------------+
| 3  | 11  | 2018-03-01 |
+----+-----+------------+
| 4  | 44  | 2018-04-01 |
+----+-----+------------+
| 6  | 44  | 2018-04-02 |
+----+-----+------------+
| 5  | 22  | 2018-05-01 |
+----+-----+------------+

In this table, ID is the Primary Key. NUM is the random value and CREATED is the Date.

My Query:

SELECT
  DATE_FORMAT(CREATED,'%Y-%m') AS Month,
  COUNT(NUM) AS TotalNum,
  COUNT(DISTINCT(NUM)) AS UniqueNum
FROM
  `numbers`
GROUP BY
  DATE_FORMAT(CREATED,'%Y-%m')

Result of My Query:

+---------+----------+-----------+
| Month   | TotalNum | UniqueNum |
+---------+----------+-----------+
| 2018-01 |    1     |     1     |
+---------+----------+-----------+
| 2018-02 |    1     |     1     |
+---------+----------+-----------+
| 2018-03 |    1     |     1     |
+---------+----------+-----------+
| 2018-04 |    2     |     1     |
+---------+----------+-----------+
| 2018-05 |    1     |     1     |
+---------+----------+-----------+

But my Expected Result is :

+---------+----------+-----------+
| Month   | TotalNum | UniqueNum |
+---------+----------+-----------+
| 2018-01 |    1     |     1     |
+---------+----------+-----------+
| 2018-02 |    1     |     1     |
+---------+----------+-----------+
| 2018-03 |    1     |     0     |
+---------+----------+-----------+
| 2018-04 |    2     |     1     |
+---------+----------+-----------+
| 2018-05 |    1     |     0     |
+---------+----------+-----------+

Result of UniqueNum should be 0 in Month 2018-03. Because NUM 11 is already exists in Month 2018-01.

Also Result of UniqueNum should be 0 in Month 2018-05 . Because NUM 22 already exists in Month 2018-02.

I want to find the Unique number for every Month. How can I get my expected result by updating my MySQL Query? Please help.

mysql
2 Answers

You just have to join the table to itself and return there only the numbers that don't exist with a previous CREATED date.

SELECT
  DATE_FORMAT(n.CREATED,'%Y-%m') AS Month,
  COUNT(n.NUM) AS TotalNum,
  COUNT(un.NUM) AS UniqueNum
FROM
  `numbers` n
LEFT JOIN (SELECT ID, NUM FROM numbers n1 WHERE NOT EXISTS (SELECT 1 FROM numbers n2 WHERE n1.NUM = n2.NUM AND n2.CREATED < n1.CREATED)) un ON n.ID = un.ID 
GROUP BY
  DATE_FORMAT(n.CREATED,'%Y-%m')
user_4474890

The solution that usually works for me is nested queries Hope this helps

SELECT
    Month,
    SUM(NUMCNT) AS TotalNum,
    COUNT(NUM) AS UniqueNum
FROM
    (
SELECT
    DATE_FORMAT(CREATED,'%Y-%m') AS Month,
    NUM,
    COUNT(NUM) AS NUMcnt
FROM
    `numbers`
GROUP BY
    Month,
    NUM
) a
;
user_42070550
Related