asked on

How to count the number of rows per SELECT and group all results by date?

Experts,

I have the following query which produces the correct count.

I need to update the query so that I can add the counts by day and not just an aggregate total.

Is this possible? I tried using GROUP BY CAST(created AS DATE) but, I get error stating that my subquery returns 2 rows.

I would like the resulting array to give me a total per day, and the 'created' date.

Something like this...

[
['2015-11-01', total],
['2015-11-02', total],
['2015-11-03', total],
['2015-11-04', total],
....
['2015-11-09', total]
]

SELECT (
SELECT COUNT(*) FROM tpmg_snmo.encounters WHERE med_search='1' AND lang_pref=med_lang AND
 fac_pref IN ('DRV', 'ANT', 'LVM', 'MTZ', 'SHA', 'PLS', 'BSR', 'WCR') AND CAST(created AS DATE) BETWEEN
 '2015-11-01' AND '2015-11-09') 
+ (SELECT COUNT(*) FROM tpmg_snmo.encounters WHERE ped_search='1' AND
 lang_pref=ped_lang AND fac_pref IN ('DRV', 'ANT', 'LVM', 'MTZ', 'SHA', 'PLS', 'BSR', 'WCR') AND CAST(created AS DATE) BETWEEN '2015-11-01' AND '2015-11-09') 
+ (SELECT COUNT(*) FROM tpmg_snmo.encounters
 WHERE gyn_search='1' AND lang_pref=gyn_lang AND fac_pref IN ('DRV', 'ANT', 'LVM', 'MTZ', 'SHA', 'PLS'
, 'BSR', 'WCR') AND CAST(created AS DATE) BETWEEN '2015-11-01' AND '2015-11-09') 
+ (SELECT COUNT(*) FROM
 tpmg_snmo.encounters WHERE fam_search=1 AND lang_pref=fam_lang AND fac_pref IN ('DRV', 'ANT', 'LVM'
, 'MTZ', 'SHA', 'PLS', 'BSR', 'WCR') AND CAST(created AS DATE) BETWEEN '2015-11-01' AND '2015-11-09') 
AS total

Open in new window

PortletPaul

regarding this:

AND CAST(created AS date) BETWEEN '2015-11-01' AND '2015-11-09'

It is not efficient to alter every row of data to suit 2 constants (those dates)

This is way more efficient, and gets the same result:

AND created >= '2015-11-01' AND created < '2015-11-10'

For more on this topic please see: "Beware of Between"

I'll address you counting question soon

PortletPaul

Try this please:

SELECT
      CAST(created AS date) AS created
    , COUNT(CASE
            WHEN med_search = '1' AND
            lang_pref = med_lang THEN created
            ELSE NULL
      END) AS med_count
    , COUNT(CASE
            WHEN ped_search = '1' AND
            lang_pref = ped_lang THEN created
            ELSE NULL
      END) AS ped_count
    , COUNT(CASE
            WHEN gyn_search = '1' AND
            lang_pref = gyn_lang THEN created
            ELSE NULL
      END) AS gyn_count
    , COUNT(CASE
            WHEN fam_search = 1 AND
            lang_pref = fam_lang THEN created
            ELSE NULL
      END) AS fam_count
FROM tpmg_snmo.encounters
WHERE fac_pref IN ('DRV', 'ANT', 'LVM', 'MTZ', 'SHA', 'PLS', 'BSR', 'WCR')
      AND created >= '2015-11-01'
      AND created < '2015-11-10'
GROUP BY
      CAST(created AS date)
;

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

evibesmusic

ASKER

Perfect. Thank you. Works as needed.

Question: If I am using the Between statement in other queries do you think it is wise to update them to use:

created >= '2015-11-01' AND created < '2015-11-10'

PortletPaul

It is wise to stop altering every row of data to suit 2 constant dates

So please dont do it in future queries,
I really cannot know about exiting queries. If the change is simple to do then maybe. Im afraid you have to judge if it is worthwhile.

I would consider including this change in any query i have to revise