autarq autarq - 4 months ago 35
SQL Question

MySQL: select diverse statistics

I have a following setup:

-------------------------
| reference | t_stamp |
-------------------------
| 1 | 2016-07-08|
| 1 | 2016-06-10|
| 1 | 2016-06-01|
| 2 | 2016-05-23|
-------------------------


I need to select unique
reference
rows with 1) the overall count by
reference
and 2) a separate count of last month from
t_stamp
.

The result should be:

------------------------------------
| reference | overall | last_month |
------------------------------------
| 1 | 3| 2 |
| 2 | 1| 0 |
------------------------------------


The first one is easy with
select reference, count(reference) overall from tbl1 group by reference


How can I accomplish the second one in the same statement?

Thanks

Answer

You can implement this kind of conditional count using sum:

sum(month(current_date - interval 1 month) = month(t_stamp))

Which can be null, in case there are no rows for the previous month. If you want to fetch 0 instead, you can use coalesce(<...>, 0).

Comments