is invalid in the select list because it is not co

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

is invalid in the select list because it is not co

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2014-10-13 : 16:00:36

When I trying to execute below query I am getting error saying that

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

But if I add that ErDateTime column in Groupby getting wrong results. I need to get the results based on group by Location. How can I solve this problem? Can any one please help me on this?

Declare @CensusDate DateTime
set @CensusDate = '09/18/2012'

SELECT convert(varchar(8),ErDateTime,112) as ErDateTime ,RIGHT(CONVERT(VARCHAR, ErDateTime, 100),7) as Time,
CASE c.LocationName WHEN 'EXPRESS CARE' THEN 'EXPRESS CARE'
ELSE 'EMERGENCY DEPARTMENT' END AS Location,
COUNT(c.LocationName) as Total
FROM
AdmDischarge AS a
INNER JOIN BarVisits AS b ON a.VisitID = b.VisitID
INNER JOIN AdmVisits AS c ON a.VisitID = c.VisitID
WHERE
1=1
AND a.ErDateTime >= @CensusDate
Group By (CASE c.LocationName WHEN 'EXPRESS CARE' THEN 'EXPRESS CARE'
ELSE 'EMERGENCY DEPARTMENT' END)
order by a.ErDateTime

Archana

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 16:04:16

It means what it says. When using Group by, the items in the select list have to be in either the group by clause or be aggregated (sum, avg, count, max, etc). That applies to the EdDateTime column. Depending on what you want to see, try MAX() or MIN()

archana23
Yak Posting Veteran

89 Posts

Posted - 2014-10-13 : 16:10:41

I want to get all the ErDateTime values if I add Max() or Min() I will loose my data right?

I want to calculate total ED patients for each day .

Can you suggest me which fn I can use so that my data cant be disturbe.

Archana

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 16:17:10

Well you have a choice: Show all the erDateTime values (no aggregation) or do a group by with aggregation. You can't do both.

archana23
Yak Posting Veteran

89 Posts

Posted - 2014-10-13 : 16:25:14

I didn't get you.. :(

Can you please change my query according to your first choice ( i.e Show all the erDateTime values (no aggregation) )

Archana

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 19:30:37

[code]
SELECT CONVERT(varchar(8) , ErDateTime , 112) AS ErDateTime
, RIGHT( CONVERT( varchar , ErDateTime , 100) , 7) AS Time
, CASE c.LocationName
WHEN 'EXPRESS CARE' THEN 'EXPRESS CARE'
ELSE 'EMERGENCY DEPARTMENT'
END AS Location
--, COUNT( c.LocationName) AS Total
FROM
AdmDischarge AS a
iNNER JOIN BarVisits AS b
ON a.VisitID = b.VisitID
INNER JOIN AdmVisits AS c
ON a.VisitID = c.VisitID
WHERE 1 = 1 AND a.ErDateTime >= @CensusDate
ORDER BY CASE c.LocationName
WHEN 'EXPRESS CARE' THEN 'EXPRESS CARE'
ELSE 'EMERGENCY DEPARTMENT'
END , a.ErDateTime;
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-13 : 20:00:05

You can use a CTE or derived table to get around this issue:


SELECT 
	convert(varchar(8),ErDateTime,112) as ErDateTime ,RIGHT(CONVERT(VARCHAR, ErDateTime, 100),7) as Time, 
	CASE t.Location WHEN 'EXPRESS CARE' THEN 'EXPRESS CARE' 
		ELSE 'EMERGENCY DEPARTMENT' 
	END AS Location, 
	t.Total
FROM AdmDischarge AS a 
JOIN BarVisits AS b ON a.VisitID = b.VisitID 
JOIN AdmVisits AS c ON a.VisitID = c.VisitID
JOIN
(
	SELECT 
		c.LocationName AS Location,
		COUNT(c.LocationName) as Total
	FROM AdmDischarge AS a 
	JOIN BarVisits AS b ON a.VisitID = b.VisitID 
	JOIN AdmVisits AS c ON a.VisitID = c.VisitID
	WHERE a.ErDateTime >= @CensusDate
	GROUP BY c.LocationName
) t
ON c.LocationName = t.Location
WHERE a.ErDateTime >= @CensusDate
order by a.ErDateTime

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources