Column 'result.result_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Question

Column 'result.result_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sutton, Jonathan 21

I have a query that I have created, when I run it I get these errors:

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

select 
        MAX(p.patient_name) 'Dogs Name',
        MAX(rq.print_note) 'Vial #',
        MAX(rq.collect_date) 'Collect Date',
        (select result from result r2
            where panel_test_id = '1196'
            and r.result_id = r2.result_id) 'Cortisol (ug/dL)',
        (select result from result r2
            where panel_test_id = '1197'
            and r.result_id = r2.result_id) 'Creatinine (mg/dL)',
        (select result from result r2
            where panel_test_id = '1195'
            and r.result_id = r2.result_id) 'UC:Cr (Ratio)'
    from result r
    left join req_panel rp
        on r.req_panel_id = rp.req_panel_id
    left join requisition rq
        on rp.requisition_id = rq.requisition_id
    left join vw_patient p
        on rq.patient_eid = p.patient_eid
    Where panel_test_id in ('1196','1197','1195')
    group by rp.req_panel_id
    order by 3 desc

Sutton, Jonathan 21

This is my original query, and my intention is to join the three "duplicate" lines into one:

select   
		(p.patient_name) 'Dogs Name',  
		(rq.print_note) 'Vial #',  
		(rq.collect_date) 'Collect Date',  
		(select result from result r2  
			where panel_test_id = '1196'  
			and r.result_id = r2.result_id) 'Cortisol (ug/dL)',  
		(select result from result r2  
			where panel_test_id = '1197'  
			and r.result_id = r2.result_id) 'Creatinine (mg/dL)',  
		(select result from result r2  
			where panel_test_id = '1195'  
			and r.result_id = r2.result_id) 'UC:Cr (Ratio)'  
	from result r  
	left join req_panel rp  
		on r.req_panel_id = rp.req_panel_id  
	left join requisition rq  
		on rp.requisition_id = rq.requisition_id  
	left join vw_patient p  
		on rq.patient_eid = p.patient_eid  
	Where panel_test_id in ('1196','1197','1195')  
	order by 3 desc

Accepted answer

0 additional answers

Your answer

Sutton, Jonathan 21 Reputation points

2021-11-22T20:28:59.54+00:00

This is my original query, and my intention is to join the three "duplicate" lines into one:

select (p.patient_name) 'Dogs Name', (rq.print_note) 'Vial #', (rq.collect_date) 'Collect Date', (select result from result r2 where panel_test_id = '1196' and r.result_id = r2.result_id) 'Cortisol (ug/dL)', (select result from result r2 where panel_test_id = '1197' and r.result_id = r2.result_id) 'Creatinine (mg/dL)', (select result from result r2 where panel_test_id = '1195' and r.result_id = r2.result_id) 'UC:Cr (Ratio)' from result r left join req_panel rp on r.req_panel_id = rp.req_panel_id left join requisition rq on rp.requisition_id = rq.requisition_id left join vw_patient p on rq.patient_eid = p.patient_eid Where panel_test_id in ('1196','1197','1195') order by 3 desc

Answer 1

I think is what you might be looking for. But note that since I don't have your tables, it is a bit of a guessing game:

select (p.patient_name) 'Dogs Name',
       (rq.print_note) 'Vial #',
       (rq.collect_date) 'Collect Date',
       r."Cortisol (ug/dL)", r."Creatinine (mg/dL)", r."UC:Cr (Ratio)"
    from (select req_panel_id,
                 min(case when panel_test_id = '1196' then result end) AS "Cortisol (ug/dL)", 
                 min(case when panel_test_id = '1197' then result end) AS "Creatinine (mg/dL)", 
                 min(case when panel_test_id = '1195' then result end) AS "UC:Cr (Ratio)"
          from   result
          Where panel_test_id in ('1196','1197','1195')  
          group by req_panel_id) as r
    left join req_panel rp
        on r.req_panel_id = rp.req_panel_id
    left join requisition rq
        on rp.requisition_id = rq.requisition_id
    left join vw_patient p
        on rq.patient_eid = p.patient_eid
ORDER BY "Collect Date"

Sutton, Jonathan 21 Reputation points

2021-11-22T23:46:30.507+00:00

That was perfect! I will research this, so I may implement it in other cases. Thank you so much!

Share via

Column 'result.result_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

0 additional answers

Your answer