SQL query count for each specified date

Question

I have a SQL query I am running on a mysql database that looks like this...

    $count0 = $wpdb->get_results("

 SELECT *, COUNT(*) as TotalValueCount 
   FROM
   wp_rg_lead
   INNER JOIN wp_rg_lead_detail ON
   wp_rg_lead.id=wp_rg_lead_detail.lead_id
   WHERE wp_rg_lead.form_id = '46'
   AND  cast(date_created as date) >= current_date - interval '7' day
   AND field_number = '18'
   GROUP BY value
            ");

    foreach ( $count0 as $page ) {
   echo $repid_field . ' - ' . $page->form_id . ' -  ' . $page->value .  ' - ' . $page->lead_id . ' - ' . $page->date_created.' - ' . $page->TotalValueCount. '<br/>';

What I want to do with this however is return a count for each value for each of the previous 7 days, so I can get results something like this...

value  |  01/01/2014  |  01/01/2014  |  01/01/2014
--------------------------------------------------
AA01         34              23            12
BR65         3               65            65
YR76         45              12            65

Is this something I would need to do with the php function afterwards or can this be done with the SQL query itself?

Imho, you should not try to get the expected output within mysql itself. Get the desired data grouping by value and date, and then use/format it with php/html. — BMN
– BMN, Commented Dec 2, 2014 at 8:38

Dmitry Grekov · Accepted Answer · 2014-12-02 09:30:29Z

1

this one should do the job

SELECT   value, 
        sum(case 
                when cast(date_created as date) = current_date - interval '7' day 
                then 1 else 0 end
            ) d7,
        ...
        sum(case 
                when cast(date_created as date) = current_date - interval '1' day 
                then 1 else 0 end
            ) d1
FROM  wp_rg_lead
INNER JOIN wp_rg_lead_detail ON
wp_rg_lead.id=wp_rg_lead_detail.lead_id
WHERE wp_rg_lead.form_id = '46'
AND  cast(date_created as date) >= current_date - interval '7' day
AND field_number = '18'
GROUP BY value

answered Dec 2, 2014 at 9:30

Dmitry Grekov

6883 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fightstarr20 Over a year ago

The date_created field is formatted like this '2014-12-01 14:23:00' - Is there a way to replace the d1,d2,d3 etc.. with the date itself?

Dmitry Grekov Over a year ago

I think the answer is rather no. I'd better leave such formating for php/html.

Collectives™ on Stack Overflow

SQL query count for each specified date

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related