Query Syntax Error

Question

Query Syntax Error

pop

SSCrazy

Points: 2130
More actions
June 1, 2005 at 2:58 pm

#67092

When I run the following:
SELECT DISTINCT [CheckNumbersWithClaims].pidate, Sum([CheckNumbersWithClaims].TopAmt1) AS SumOftopamt1, Count([CheckNumbersWithClaims].TopAmt1) AS CountOftopamt1,
cast((select CASE WHEN [SvLOB]='HP' And [SvSrcX]='M' THEN topamt1 ELSE 0 END) as money) AS HPMHS_topamt
INTO CheckDateSummary
FROM [CheckNumbersWithClaims]
GROUP BY [CheckNumbersWithClaims].pidate
I receive the following errors:
Server: Msg 8120, Level 16, State 1, Line 3
Column 'CheckNumbersWithClaims.TopAmt1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 3
Column 'CheckNumbersWithClaims.SvLob' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 3
Column 'CheckNumbersWithClaims.SvSrcX' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Can anyone point me in the right direction on my error(s)here? Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

You are summing and counting. If you have multiple records where your CASE statement can be either 1 or 0, this may cause problems.

You may want to do an Inner Join on the CheckNumbersWithClaims table and derive your answer in that fashion - I am assuming you do not want to GROUP on SvLOB and SvSrcX.

I wasn't born stupid - I had to study.

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 2

Sure...

cast((select CASE WHEN [SvLOB]='HP' And [SvSrcX]='M' THEN topamt1 ELSE 0 END) as money)

is invalid just like the Q/A said... you can't put any column which is not contained into an aggregate function (SUM, COUNT, etc) or exists in the GROUP BY clause... and in this single CASE sentence, you used three columns not contained in aggregate functions neither in the Group By clause... so, the Q/A raises 3 errors...

So...

SELECT a, b, c // or SELECT a, (CASE WHEN b>0 THEN c ELSE 0)

FROM Table1

GROUP BY a

is invalid

SELECT a, SUM(b), COUNT(c)

FROM Table1

GROUP BY a

is VALID.

Note that not just SELECTED columns must fit this rules, also columns used in a CASE stamentey must... think about it...

This is your query...

SELECT DISTINCT [CheckNumbersWithClaims].pidate, Sum([CheckNumbersWithClaims].TopAmt1) AS SumOftopamt1, Count([CheckNumbersWithClaims].TopAmt1) AS CountOftopamt1,

cast((select CASE WHEN [SvLOB]='HP' And [SvSrcX]='M' THEN topamt1 ELSE 0 END) as money) AS HPMHS_topamt

INTO CheckDateSummary

FROM [CheckNumbersWithClaims]

GROUP BY [CheckNumbersWithClaims].pidate

so, what result would be if there is some data in the table for which exists, i.e., two records with the same pidate but diferent SvLOB, since the query is grouping by pidate, which one of the two possibles SvLOB is used in the CASE? Also... which TopAmt1 would be selected?

I can't figure what you want to obtain as result... if you submit some examples of Original Data and Result expected, I would, probably, help you in a better way.

Nicolas Donadio

pop SSCrazy Points: 2130 More actions · Answer 3

Thanks guys. This is originally an Access Query:

SELECT DISTINCTROW [CheckNumersWith Claims].pidate, Sum([CheckNumersWith Claims].TopAmt1) AS SumOftopamt1, Count([CheckNumersWith Claims].TopAmt1) AS CountOftopamt1, CCur(Sum(IIf(([SvLOB]='HP' And [SvSrcX]='M'),[topamt1],0))) AS HPMHS_topamt, CCur(Sum(IIf(([svLOB]='HP' And [svSrcX]='C'),[topamt1],0))) AS HPMCS_topamt, CCur(Sum(IIf(([svLOB]='SP' And [svSrcx]='M'),[topamt1],0))) AS SPMHS_topamt, CCur(Sum(IIf(([svLOB]='SP' And [svsrcx]='C'),[topamt1],0))) AS SPMCS_topamt INTO CheckDateSummary

FROM [CheckNumersWith Claims]

GROUP BY [CheckNumersWith Claims].pidate;

I tried:

SELECT DISTINCT [CheckNumbersWithClaims].pidate, Sum([CheckNumbersWithClaims].TopAmt1) AS SumOftopamt1, Count([CheckNumbersWithClaims].TopAmt1) AS CountOftopamt1,

cast((select CASE WHEN [SvLOB]='HP' And [SvSrcX]='M' THEN topamt1 ELSE 0 END) as money) AS HPMHS_topamt,

cast((select CASE WHEN [SvLOB]='HP' And [SvSrcX]='C' THEN topamt1 ELSE 0 END) as money) AS HPMCS_topamt,

cast((select CASE WHEN [SvLOB]='SP' And [SvSrcX]='M' THEN topamt1 ELSE 0 END) as money) AS SPMHS_topamt,

cast((select CASE WHEN [SvLOB]='SP' And [SvSrcX]='C' THEN topamt1 ELSE 0 END) as money) AS SPMCS_topamt

INTO CheckDateSummary

FROM [CheckNumbersWithClaims]

GROUP BY [CheckNumbersWithClaims].pidate

It's a SELECT INTO.

The data looks lke this:

pidate, SumOftopamt1, CountOftopamt1, HPMHS_topamt, HPMCS_topamtSPMHS_topamt, SPMCS_topamt

1/3/1999, $1,219,550.18, 7268, $0.00, $1,084,772.35, $0.00, $134,777.83

Any ideas on a way to get this done. Thanks guys.

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 4

Again... please, post tha tables structures and examples of original and desired data... and any meaningful constraint and/or relation .

Nicolas

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 5

well.. again me!

If I understood your needs... you want to obtain the TOAL AMOUNT (SUM) of TopAmt1 And some partial sums depending on some other values... so, your are missing the SUM function...

So.. try using SUM( CAST(SELECT... ) &nbsp and tell me if it worked... or clarify my mind if I get wrong...

Nicolas

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 6

Still.... again me!!!

Look at thi query... I think it's something like this what you want, no?

set nocount on

create table #tmp__1 (piDate smalldatetime, Val1 char(1), Amount int)

insert into #tmp__1 values( getdate(), 'A', 10 )

insert into #tmp__1 values( getdate(), 'B', 15 )

insert into #tmp__1 values( getdate(), 'B', 20 )

insert into #tmp__1 values( getdate()-1, 'A', 10 )

insert into #tmp__1 values( getdate()-1, 'B', 20 )

insert into #tmp__1 values( getdate()-1, 'A', 30 )

insert into #tmp__1 values( getdate()-1, 'B', 70 )

select piDate, sum(Amount) as TotalSum,

sum( case Val1 when 'A' then Amount else 0 end) as ASum,

sum( case Val1 when 'B' then Amount else 0 end) as BSum

from #tmp__1

group by pidate

drop table #tmp__1

set nocount off

Steve Fine Ten Centuries Points: 1134 More actions · Answer 7

Have you tried putting

cast((select CASE WHEN [SvLOB]='HP' And [SvSrcX]='M' THEN topamt1 ELSE 0 END)

into the group statement? Usually works for me because you are summing or counting by which ever case it is.

pop SSCrazy Points: 2130 More actions · Answer 8

Thanks for all the replies ! I got the following to work:

SELECT DISTINCT [CheckNumbersWithClaims].pidate, Sum([CheckNumbersWithClaims].TopAmt1) AS SumOftopamt1, Count([CheckNumbersWithClaims].TopAmt1) AS CountOftopamt1,

CAST(SUM(CASE WHEN ([SvLOB]='HP' And [SvSrcX]='C') THEN topamt1 ELSE 0 END) as money) AS HPMCS_topamt,

CAST(SUM(CASE WHEN ([SvLOB]='SP' And [SvSrcX]='M') THEN topamt1 ELSE 0 END) as money) AS SPMHS_topamt,

CAST(SUM(CASE WHEN ([SvLOB]='SP' And [SvSrcX]='C') THEN topamt1 ELSE 0 END) as money) AS SPMCS_topamt

INTO CheckDateSummary

FROM [CheckNumbersWithClaims]

GROUP BY [CheckNumbersWithClaims].pidate