0

I recieve the following error with the code below:

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

Code is as follows:

set dateformat dmy 
select qualitystd.qualitystd_id, qualitystd.qualitystd, count(edrec.qualitystd_id) as n, targetreacheddate, treelocation1
from qualitystd    
join (      
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1
from reports r
    right join(
        select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date      
        from edit_history      
        where (edit_date < dateadd(day, 1, '11/21/2014'))
        group by edithistory_id,qualitystd_id    
    ) res
on r.edithistory_id = res.edithistory_id
where  r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec
on edrec.qualitystd_id = qualitystd.qualitystd_id
group by qualitystd.qualitystd_id   
order by qualitystd.qualitystd_id

Can anyone tell me where im going wrong? The first 2 selects (from the inside out) work fine. It is just the last one that is causing problems. I thought i had put the qualitystd.qualitystd_id field in the group by statement.

When i run the following code it works correctly.

select qualitystd.qualitystd_id, qualitystd.qualitystd, targetreacheddate, treelocation1
from qualitystd    
join (      
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1
from reports r
    right join(
        select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date      
        from edit_history      
        where (edit_date < dateadd(day, 1, '11/21/2014'))
        group by edithistory_id,qualitystd_id    
    ) res
on r.edithistory_id = res.edithistory_id
where  r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec
on edrec.qualitystd_id = qualitystd.qualitystd_id

However, I need to add the count(qualitystd.qualitystd) or count(qualitystd.qualitystd_id) in but i just cant seem to get it to work.

Help Please...

user3681948
  • 11
  • 3
  • add qualitystd.qualitystd column in the last group by clause. – radar Nov 21 '14 at 02:54
  • possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – radar Nov 21 '14 at 02:56
  • thanks. i have found the solution. i didnt end up using the count. – user3681948 Dec 04 '14 at 03:46

1 Answers1

0

You need to remove qualitystd.qualitystd from the Select statement, you can only use those fields in select statement that are used in group by clause, so if you need to counts of different qualitystd.qualitystd_id you can use only qualitystd.qualitystd_id and count(qualitystd.qualitystd_id) as you are grouping by the data on qualitystd.qualitystd_id. So the sql statement will be something like this `

select qualitystd.qualitystd_id, count(edrec.qualitystd_id) as n, targetreacheddate, treelocation1
from qualitystd    
join (      
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1
from reports r
    right join(
        select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date      
        from edit_history      
        where (edit_date < dateadd(day, 1, '11/21/2014'))
        group by edithistory_id,qualitystd_id    
    ) res
on r.edithistory_id = res.edithistory_id
where  r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec
on edrec.qualitystd_id = qualitystd.qualitystd_id
group by qualitystd.qualitystd_id   
order by qualitystd.qualitystd_id

`

or if you need count of different qualitystd.qualitystd the sql statement will be like this

    select  qualitystd.qualitystd, count(edrec.qualitystd_id) as n, targetreacheddate, treelocation1
from qualitystd    
join (      
select r.edithistory_id, r.targetreacheddate, qualitystd_id, r.treelocation1
from reports r
    right join(
        select edithistory_id, QualityStd_ID, MAX(edit_date) AS edit_date      
        from edit_history      
        where (edit_date < dateadd(day, 1, '11/21/2014'))
        group by edithistory_id,qualitystd_id    
    ) res
on r.edithistory_id = res.edithistory_id
where  r.edithistory_id = res.edithistory_id and (targetreacheddate IS NULL) and treelocation1 = 'Illawarra Shoalhaven Local Health District' 
) edrec
on edrec.qualitystd_id = qualitystd.qualitystd_id
group by  qualitystd.qualitystd  
order by  qualitystd.qualitystd

hope this might help and solve your problem

user2797965
  • 1