Add count to SQL query

Question

I have the following SQL query and php output being run on a mysql database...

$count0 = $wpdb->get_results("      
   SELECT *
   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'
   ORDER BY value
");

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

This works great but I want to add a count to it so that when it outputs the line it tells me how many of 'value' exists.

I have had a look at the COUNT function but I am not sure where to add it in

Mitch Wheat · Accepted Answer · 2014-12-01 23:16:59Z

1

$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'
   ORDER BY value
");

edited Dec 1, 2014 at 23:16

answered Dec 1, 2014 at 23:08

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

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

5 Comments

fightstarr20 Over a year ago

Thanks, that works and adds a count but it now only returns one result and a count for all. Can it be modified to output the count for each result?

Mitch Wheat Over a year ago

you would need to group by value, but then you couldn't do a 'SELECT *'

fightstarr20 Over a year ago

Do you think I would be better off doing my counting using a php function instead? Or would sticking to doing it in the original query be more efficient?

Mitch Wheat Over a year ago

that would depend on the number of rows in the table

fightstarr20 Over a year ago

dataset is quite large so looks like doing it in the query would be the best bet then. I will look up grouping by value and see if I can achieve it that way. Thanks for the pointers

Collectives™ on Stack Overflow

Add count to SQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related