SQL 쿼리문 에러 입니다 ㅠㅠ
안녕하세요
MS-SQL 로 어떻게 DB를 막 시작한 초보 입니다.
여기저기 뒤져서 하기와 같은 쿼리문을 만들었습니다.
이것들을 Union 으로 '일자' 기준으로 합치기 해서 정열하고 싶은데요
자꾸 하기와 같은 에러가 납니다 ㅠ
"메시지 8120, 수준 16, 상태 1, 줄 1
열 'Observances.ObservanceTime'이(가) 집계 함수나 GROUP BY 절에 없으므로 SELECT 목록에서 사용할 수 없습니다."
고견을 기다리겠습니다 ㅠㅠ
//쿼리//
select Datepart(day,dateAdd(hour,0,ObservanceTime)) as '일자', sum(ObservanceCount) as 'FTP'
from Observances, SecurityChecks
where ObservanceType=1
and Observances.SecChkID=SecurityChecks.SecChkID
and SecurityChecks.tagname like 'FTP%'
and observancetime between '2015-01-01 00:00:00' and '2015-01-31 23:59:59'
union
select Datepart(day,dateAdd(hour,0,ObservanceTime)) as '일자', sum(ObservanceCount) as 'SSH'
from Observances, SecurityChecks
where ObservanceType=1
and Observances.SecChkID=SecurityChecks.SecChkID
and SecurityChecks.tagname like 'SSH%'
and observancetime between '2015-01-01 00:00:00' and '2015-01-31 23:59:59'
group by Datepart(day,dateAdd(hour,0,ObservanceTime))
order by '일자'
참고로 각각 개별 쿼리는 잘 돌아 갑니다 ㅠ
참고로 각각 개별 쿼리는 잘 돌아 갑니다 ㅠ
select
select Datepart(day,dateAdd(hour,0,ObservanceTime)) as '일자', sum(ObservanceCount) as 'FTP'
from Observances, SecurityChecks
where ObservanceType=1
and Observances.SecChkID=SecurityChecks.SecChkID
and SecurityChecks.tagname like 'FTP%'
and observancetime between '2015-01-01 00:00:00' and '2015-01-31 23:59:59'
group by Datepart(day,dateAdd(hour,0,ObservanceTime))
union
select Datepart(day,dateAdd(hour,0,ObservanceTime)) as '일자', sum(ObservanceCount) as 'SSH'
from Observances, SecurityChecks
where ObservanceType=1
and Observances.SecChkID=SecurityChecks.SecChkID
and SecurityChecks.tagname like 'SSH%'
and observancetime between '2015-01-01 00:00:00' and '2015-01-31 23:59:59'
group by Datepart(day,dateAdd(hour,0,ObservanceTime))
order by '일자'
로 해결했습니다 ㅠㅠ
댓글 달기