Why Is This Query Complaining About Group Function?

[gmmastros]

You need to use a GROUP BY anytime you use an aggregate function. The HAVING clause allows you to put a filter condition on the aggregate.

The simplest thing to try is to reproduce the entire select clause in the group by with the exception of the aggregate function.

SELECT   Distinct A.MBR_SSN_NBR,
         CASE 
              when A.MBR_F_EMP_DT between '2010-07-01' and '2011-06-30' then 'NEW' 
              when A.MBR_L_EMP_DT between '2010-07-01' and '2011-06-30' then 'TERMINATED' 
              when C.mbr_stat_cd = '3' then 'DECEASED' 
         END AS STATUS,
         min(C.MBR_F_ENROL_DT) as "START DATE"
         
         
FROM     DSNP.PR01_T_MBR_EMPR A,
         DSNP.PR01_T_MBR_HIST B,
         DSNP.PR01_T_MBR_SYS C

WHERE    A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND      A.MBR_SSN_NBR=C.MBR_SSN_NBR

AND      B.MBR_HIST_SVC_CR_DT BETWEEN '2010-07-01' AND '2011-06-30'
Group By A.MBR_SSN_NBR,
         CASE 
              when A.MBR_F_EMP_DT between '2010-07-01' and '2011-06-30' then 'NEW' 
              when A.MBR_L_EMP_DT between '2010-07-01' and '2011-06-30' then 'TERMINATED' 
              when C.mbr_stat_cd = '3' then 'DECEASED' 
         END

Also... you won't need the distinct in the select clause because the group by will cause each row in the output to be distinct anyway.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lrdave36]

Thanks George! I didn't know you could put the whole case statement in the Group By. Just to clarify, I don't need Group By if I am returning only the min function results. I think the fact that I am also selecting the mbr_ssn_nbr field requires the Group By. Makes sense now.

 

[gmmastros]

You're right! You only need a group by clause when you include a column in the select clause that does not include an aggregate.

When you only have aggregates in the select clause (and no group by), you will get the aggregated value for the whole table (unless there is also a where clause).

Anyway... I'm glad I could help.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

george, looks like your bad monday extended into tuesday ;-)

i was gonna take you to task for your comment that a GROUP BY clause is needed when you want to use an aggregate function, but i see the original poster already dinged you for that

the classic example, of course, is the table row count query --

SELECT COUNT(*) FROM daTable

see? no GROUP BY

anyhow, i wanted to make a comment on your remark that the simplest thing to try is to repeat all the non-aggregate expressions from the SELECT clause in the GROUP BY clause

i'm pretty sure you can simply substitute the column alias for the entire CASE expression in the GROUP BY

SELECT a.mbr_ssn_nbr
     , CASE WHEN a.mbr_f_emp_dt BETWEEN '2010-07-01' 
                                    AND '2011-06-30' 
            THEN 'NEW'       
            WHEN a.mbr_l_emp_dt BETWEEN '2010-07-01' 
                                    AND '2011-06-30' 
            THEN 'TERMINATED'       
            WHEN c.mbr_stat_cd = '3' 
            THEN 'DECEASED'  
        END AS status
     , MIN(c.mbr_f_enrol_dt) AS "START DATE"              
  FROM dsnp.pr01_t_mbr_empr AS A
INNER
  JOIN dsnp.pr01_t_mbr_hist AS B
    ON b.mbr_ssn_nbr = a.mbr_ssn_nbr
   AND b.mbr_hist_svc_cr_dt BETWEEN '2010-07-01' 
                                AND '2011-06-30'
INNER
  JOIN dsnp.pr01_t_mbr_sys AS C
    ON c.mbr_ssn_nbr = a.mbr_ssn_nbr
GROUP 
    BY a.mbr_ssn_nbr
     , [red]status[/red]

another simple solution is to "push down" the guts of the query into an inline view

[red]SELECT mbr_ssn_nbr
     , status
     , MIN(mbr_f_enrol_dt) AS "START DATE" 
  FROM ([/red] SELECT a.mbr_ssn_nbr
              , CASE WHEN a.mbr_f_emp_dt BETWEEN '2010-07-01' 
                                             AND '2011-06-30' 
                     THEN 'NEW'       
                     WHEN a.mbr_l_emp_dt BETWEEN '2010-07-01' 
                                             AND '2011-06-30' 
                     THEN 'TERMINATED'       
                     WHEN c.mbr_stat_cd = '3' 
                     THEN 'DECEASED'  
                 END AS status
              , c.mbr_f_enrol_dt
           FROM dsnp.pr01_t_mbr_empr AS A
         INNER
           JOIN dsnp.pr01_t_mbr_hist AS B
             ON b.mbr_ssn_nbr = a.mbr_ssn_nbr
            AND b.mbr_hist_svc_cr_dt BETWEEN '2010-07-01' 
                                         AND '2011-06-30'
         INNER
           JOIN dsnp.pr01_t_mbr_sys AS C
             ON c.mbr_ssn_nbr = a.mbr_ssn_nbr [red]) AS foo
GROUP 
    BY mbr_ssn_nbr
     , status[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

I suppose I should have said, "I think the simplest..."

I still believe that to be true, too. Copy pasting the select columns to the bottom of the query is pretty simple. I did not try to mislead anyone in to thinking that it was the only way to accomplish this. If I did, I am truly sorry for that.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

i'm pretty sure you can simply substitute the column alias for the entire CASE expression in the GROUP BY

I'm pretty sure that you cannot do that (I just tested it).

You can use column aliases in an Order By clause. I'm not 100% sure, but I think that is the only place you can use a column alias (unless you are using an inline view). You cannot use a column alias in the group by clause, the having clause, the join clause, or the where clause (I tested these too).


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

sheeeeit, bitten by sql server's non-standard syntax again

GROUP BY on a column alias ~is~ valid SQL-99

that's, what, only 12 years ago

so, i offer my apologies





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon