Get count on two different date columns and group by date

Question

I have table containing two DATE columns. TS_customer and TS_verified

I am searching for a way to get a result where in the first column I have dates where either someone created a user (TS_customer) or someone got verified (TS_verified).

In the second column I want count(TS_customer) grouped by the first column. The third column I want count(TS_verified) grouped by the first column.

It might be 0 customers verified on a sign up date, and in another case 0 signups on a date someone got verified.

I guess it should be an easy one, but I've spent so many hours on it now. Would really appreciate some help. I need this for a graph in excel, so i basicly want how many customers signed up and how many got verified one day without having the hassle to have two selects and combinding them manually.

EDIT: link to SQLfiddle http://sqlfiddle.com/#!2/b14fc/1/0

Thanks

As you are new here! This is the link for you know where to do it: http://sqlfiddle.com/ there you can create a table and insert some example data so we can understand better your problem. — Jorge Campos, Oct 27 '13 at 00:42
Thanks! I don't really see how to export it in a more suitable way, but I'll look around for it. This is the link http://sqlfiddle.com/#!2/b14fc/1/0 — dojzi, Oct 27 '13 at 00:48
You write "in the first column [of my result set I want] the distinct dates of both TS_customer and TS_verified". Are you saying you want two dates displayed? I don't think you are, but it's not perfectly clear. — O. Jones, Oct 27 '13 at 01:13
Ollie: I just need dates in the first column of the result set that I want. Where these dates come from I thought would be good if I can et them from where someone have either signed up or verified their account — dojzi, Oct 27 '13 at 01:19

score 4 · Accepted Answer · answered Oct 27 '13 at 01:36

First, we need the list of days.

That looks like this http://sqlfiddle.com/#!2/b14fc/14/0:

   SELECT DISTINCT days
     FROM (
       SELECT DISTINCT DATE(TS_customer) days
         FROM customer
        UNION 
       SELECT DISTINCT DATE(TS_verified) days
         FROM customer

     ) AS alldays
 WHERE days IS NOT NULL
 ORDER BY days

Next we need a summary of customer counts by day. That's pretty easy http://sqlfiddle.com/#!2/b14fc/16/0:

SELECT DATE(TS_customer) days, COUNT(TS_customer)
  FROM customer
 GROUP BY days

The summary of verifications by day is similarly easy.

Next we need to join these three subqueries together http://sqlfiddle.com/#!2/b14fc/29/0.

SELECT alldays.days, custcount, verifycount
  FROM (
           SELECT DISTINCT DATE(TS_customer) days
             FROM customer
            UNION 
           SELECT DISTINCT DATE(TS_verified) days
             FROM customer
       ) AS alldays
   LEFT JOIN (
      SELECT DATE(TS_customer) days, COUNT(TS_customer) custcount
        FROM customer
       GROUP BY days
      ) AS cust ON alldays.days = cust.days
   LEFT JOIN (
      SELECT DATE(TS_verified) days, COUNT(TS_verified) verifycount
        FROM customer
       GROUP BY days
      ) AS verif ON alldays.days = verif.days
  WHERE alldays.days IS NOT NULL
  ORDER BY alldays.days

Finally, if you want 0 displayed rather than (null) for days when there weren't any customers and/or verifications, change the SELECT line to this http://sqlfiddle.com/#!2/b14fc/30/0.

SELECT alldays.days, 
       IFNULL(custcount,0) AS custcount, 
       IFNULL(verifycount,0) AS verifycount

See how that goes? We build up your result set step by step.

Thanks, works great! I was in the right path, building them up with three different sets and combinding them, (with union, and two normal selects) but the counts always got messed up for me. got like 3 times the value of a total count, and in some rows 6 times. — dojzi, Oct 27 '13 at 18:00
Right, you're welcome. It takes a while to figure out that you must summarize before joining or the counts will be wrong. — O. Jones, Oct 28 '13 at 00:58

Mosty Mostacho · Answer 2 · 2013-10-27T21:10:44.860

0

I'm a bit confused on why you created a fiddle that can not hold null values on the TS_Customer and then mention that the field can hold null values.

Having said that, I've modified the solution to work with null values and still be pretty efficient and simple:

SELECT days, sum(custCount) custCount, sum(verifCount) verifCount FROM (
  SELECT DATE(TS_customer) days, count(*) custCount, 0 verifCount
  FROM customer
  WHERE TS_customer IS NOT NULL
  GROUP BY days
  UNION ALL
  SELECT DATE(TS_verified) days, 0, count(*)
  FROM customer
  WHERE TS_verified IS NOT NULL
  GROUP BY days
) s
GROUP BY days

I've also created a different fiddle containing some null values here.

edited Oct 27 '13 at 21:10

answered Oct 27 '13 at 02:23

Mosty Mostacho

42,742
16
96
123

I didn't get this one to work. There can be null values in TS_Customer when someone got verified on a date where there were no new customer restrations. I added WHERE TS_customer IS NOT NULL to the top select, but there were duplicate dates in the left column (dates column) – dojzi Oct 27 '13 at 18:05
You were right on the fact that it didn't handle null values properly but I didn't test it based on the fact that your fiddle didn't allow null values on that column :) Anyway, I've provided a new (not so fast as before) solution that can handle those null values – Mosty Mostacho Oct 27 '13 at 21:12

Get count on two different date columns and group by date

2 Answers2

Linked