using where and group in a subquery

Question

below is my code that is not executing successfully. Any suggestions would be greatly appreciated. I am unable to troubleshoot, I am wondering if it is just placement?

This is the error message I receive:

Msg 8120, Level 16, State 1, Line 7
Column 'prod.dbo.prov_fee_schedule.FEE_PROV' is invalid in the select >list because it is not contained in either an aggregate function or the GROUP BY clause.

Here is my code:

SELECT * 
FROM   nwh_analytics.dbo.km_cmr_table a 
       LEFT JOIN (SELECT CASE 
                           WHEN location IN ( '03', '05', '11', '12', 
                                              '15', '20', '25', '32', 
                                              '41', '42', '49', '50', 
                                              '60', '71', '72', '81', '99' ) 
                         THEN 
                           'Non Facility' 
                           WHEN location = '*' THEN 'Facility' 
                           ELSE 'error' 
                         END AS LOC, 
                         fee_prov, 
                         fee_cpt, 
                         cpt_modifier, 
                         rate_a, 
                         rate_b, 
                         rate_c, 
                         pct 
                  FROM   prod.dbo.prov_fee_schedule 
                  WHERE  to_date >= '2017-01-01' 
                         AND fee_prov IN ( 'TUPP00' ) 
                  GROUP  BY CASE 
                              WHEN location IN ( '03', '05', '11', '12', 
                                                 '15', '20', '25', '32', 
                                                 '41', '42', '49', '50', 
                                                 '60', '71', '72', '81', '99' ) 
                 THEN 
                              'Non Facility' 
                              WHEN location = '*' THEN 'Facility' 
                              ELSE 'error' 
                            END) b 
              ON a.code = b.fee_cpt

Why do you have that GROUP BY? It is causing an error an is doing nothing. — bruno.almeida, Aug 22 '17 at 14:38
The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. (Still a question why you have the group by, though...) — jarlh, Aug 22 '17 at 14:42

score 1 · Answer 1 · answered Aug 22 '17 at 14:36

1

You should use having instead of where:

group by ...
having TO_DATE >= '2017-01-01' 
       and FEE_PROV in ('TUPP00')

answered Aug 22 '17 at 14:36

NikNik

2,191
2
15
34

score 1 · Answer 2 · answered Aug 22 '17 at 14:47

You should put all columns from SELECT list to GROUP BY (only these which don't have an aggregation). In your case - all of them.

SELECT *  FROM   nwh_analytics.dbo.km_cmr_table a 
       LEFT JOIN (SELECT CASE 
                           WHEN location IN ( '03', '05', '11', '12', 
                                              '15', '20', '25', '32', 
                                              '41', '42', '49', '50', 
                                              '60', '71', '72', '81', '99' ) 
                         THEN 
                           'Non Facility' 
                           WHEN location = '*' THEN 'Facility' 
                           ELSE 'error' 
                         END AS LOC, 
                         fee_prov, 
                         fee_cpt, 
                         cpt_modifier, 
                         rate_a, 
                         rate_b, 
                         rate_c, 
                         pct 
                  FROM   prod.dbo.prov_fee_schedule 
                  WHERE  to_date >= '2017-01-01' 
                         AND fee_prov IN ( 'TUPP00' ) 
                  GROUP  BY CASE 
                              WHEN location IN ( '03', '05', '11', '12', 
                                                 '15', '20', '25', '32', 
                                                 '41', '42', '49', '50', 
                                                 '60', '71', '72', '81', '99' ) 
                 THEN 
                              'Non Facility' 
                              WHEN location = '*' THEN 'Facility' 
                              ELSE 'error' 
                            END,
                         fee_prov, 
                         fee_cpt, 
                         cpt_modifier, 
                         rate_a, 
                         rate_b, 
                         rate_c, 
                         pct 
              ) b 
              ON a.code = b.fee_cpt

Thank you thank you thank you! Where would I place the order by code that comes from nwh_analytics.dbo.KM_CMR_table? — Kimm, Aug 22 '17 at 18:23

score 0 · Answer 3 · answered Aug 22 '17 at 14:35

0

You can use MAX(). For example: MAX(FEE_PROV) to group statements under SELECT

answered Aug 22 '17 at 14:35

kazzi

524
9
33

using where and group in a subquery

3 Answers3