CODEWITHSUNDEEP
mysql
Piotr
Piotr

Reputation: 258

MYSQL: How to get unique values to date grouped by date?

I need to find month by month way of showing year to date unique values. For example:

created_at   value
01-01-2012       a
01-01-2012       b
01-01-2012       a

01-02-2012       a
01-02-2012       a
01-02-2012       a

01-03-2012       c
01-03-2012       b
01-03-2012       b

01-04-2012       d
01-04-2012       e
01-04-2012       f

Should output:

Month   Monthly unique  Year to date unique
1       2               2
2       1               2
3       2               3
4       3               6

For monthly unique it is just a matter of group by and unique(), but it won't work for year-to-date this way. Year-to-date may be achieved by using for loop and filtering month by month since the beginning of the year, but it's a bad way I want to omit.

MRE:

CREATE TABLE t1 (
    val varchar(5),
    month int
);
(STR_TO_DATE('1-01-2012', '%d-%m-%Y'), 'a'),
(STR_TO_DATE('1-01-2012', '%d-%m-%Y'), 'b'),
(STR_TO_DATE('1-01-2012', '%d-%m-%Y'), 'a'),
(STR_TO_DATE('1-02-2012', '%d-%m-%Y'), 'a'),
(STR_TO_DATE('1-02-2012', '%d-%m-%Y'), 'a'),
(STR_TO_DATE('1-02-2012', '%d-%m-%Y'), 'a'),
(STR_TO_DATE('1-03-2012', '%d-%m-%Y'), 'c'),
(STR_TO_DATE('1-03-2012', '%d-%m-%Y'), 'b'),
(STR_TO_DATE('1-03-2012', '%d-%m-%Y'), 'b'),
(STR_TO_DATE('1-04-2012', '%d-%m-%Y'), 'd'),
(STR_TO_DATE('1-04-2012', '%d-%m-%Y'), 'e'),
(STR_TO_DATE('1-04-2012', '%d-%m-%Y'), 'f');

I created this, which gives me desired output:

select value, month
from 

(
select count(distinct(val)) as value, count(distinct(month(created_at))) as month from t1 where month <=1 union
select count(distinct(val)) as value, count(distinct(month(created_at))) as month from t1 where month <=2 union
select count(distinct(val)) as value, count(distinct(month(created_at))) as month from t1 where month <=3 union
select count(distinct(val)) as value, count(distinct(month(created_at))) as month from t1 where month <=4 

) t2

But it's also awful and inconvenient for filtering the main table. How to do it in efficient way?

Upvotes: 0

Views: 179

Answers (2)

slaakso
slaakso

Reputation: 9050

You can do:

select 
    d.month as 'Month', 
    count(distinct d.value) as 'Monthly unique', 
    ytdu as 'Year to date unique'
from data d
  join (
    select y.month, count(distinct d2.value) as ytdu
    from data y
      join data d2 on d2.month<=y.month
    group by y.month
  ) as q
where d.month=q.month
group by d.month, ytdu

See db-fiddle

Upvotes: 1

etsuhisa
etsuhisa

Reputation: 1758

You can get the expected result by performing subqueries for "Monthly unique" and "Year to date unique" and then combining the results.

select
  s1.month as "Month",
  s1.cnt as "Monthly unique",
  s2.cnt as "Year to date unique"
from
  -- for "Monthly unique"
  ( select month,count(distinct value1) as cnt
      from table1 group by month) as s1
  inner join
  -- for "Year to date unique"
  ( select t2.month,count(distinct t1.value1) as cnt
      from table1 as t1 cross join table1 as t2
        on t1.month<=t2.month
      group by t2.month) as s2
  on s1.month=s2.month

dbfiddle

Upvotes: 1

Related Questions