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

evibesmusicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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

PortletPaulEE Topic AdvisorCommented:
or this if for the single counted result
SELECT
      CAST(created AS date) AS created
    , COUNT(CASE
            WHEN med_search = '1' AND
            lang_pref = med_lang THEN created
            ELSE NULL
      END)
    + COUNT(CASE
            WHEN ped_search = '1' AND
            lang_pref = ped_lang THEN created
            ELSE NULL
      END)
    + COUNT(CASE
            WHEN gyn_search = '1' AND
            lang_pref = gyn_lang THEN created
            ELSE NULL
      END)
    + COUNT(CASE
            WHEN fam_search = 1 AND
            lang_pref = fam_lang THEN created
            ELSE NULL
      END) AS tot_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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
evibesmusicAuthor Commented:
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'
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.