June 6, 2011 at 5:03 pm
Hello -
I am trying to run the following query:
Select column1, column2, column3,
CASE
WHEN ((select count(distinct U.UPC) from dbo.Specialty_UPCs as U where C.UPC = U.UPC) > 0)
THEN 'Y'
ELSE 'N'
END AS UPC_Ind */
,sum(column4) AS 'Column 4'
,sum(column5( AS 'Column 5'
FROM dbo.Archive AS C
GROUP BY
column1,
column2,
column3
But I am getting the following error: "Column 'dbo.Archive.UPC' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
I understand what the error is saying, but I do NOT want to group by UPC. That makes the result set 100X larger and simply don't want the data broken down by UPC. How else can I accomplish this without using UPC in the GROUP BY clause?
I am getting the same error this with version:
CASE WHEN EXISTS((select U.UPC from dbo.Specialty_UPCs as U where C.NDC = U.NDC))
THEN 'Y'
ELSE 'B'
END AS 'Specialty_Ind'
June 6, 2011 at 5:05 pm
Do the count as a subquery and join to it to avoid the outer query caring about it.
Do what Jason recommended below, much cleaner.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 6, 2011 at 5:07 pm
Throw a MAX() around U.UPC. You are just doing an existence check on it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 5:19 pm
Like this:??
CASE
WHEN ((select count(distinct MAX(U.UPC)) from dbo.Specialty_UPCs as U where C.UPC = U.UPC) > 0)
THEN 'Y'
ELSE 'N'
END AS UPC_Ind */
I am still getting the same error.
June 6, 2011 at 5:23 pm
Triality (6/6/2011)
Like this:??
CASE
WHEN ((select count(distinct MAX(U.UPC)) from dbo.Specialty_UPCs as U where C.UPC = U.UPC) > 0)
THEN 'Y'
ELSE 'N'
END AS UPC_Ind */
I am still getting the same error.
More like this
CASE WHEN EXISTS((select top 1 Max(U.UPC) from dbo.Specialty_UPCs as U where C.UPC = U.UPC))
THEN 'Y'
ELSE 'B'
END AS 'Specialty_Ind'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 5:35 pm
I am still getting the same error with that code 🙁
June 6, 2011 at 5:38 pm
Is there more to the query than what you have shown us?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 6:01 pm
The only thing I left out was the WHERE clause.
June 6, 2011 at 6:28 pm
What is in your where clause?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 6:52 pm
Below is the entire query. Hopefully this helps!
select
[YearMonth]
,[ID]
,[STATE]
,[ORG_NBR]
,[MEMBERSHIP_CODE]
,column6
,column7
,column8
,CASE WHEN EXISTS((select top 1 MAX(U.UPC) from dbo.Specialty_UPCs AS U WHERE C.UPC = U.UPC))
THEN 'Y'
ELSE 'N'
END AS 'Specialty_Ind'
,
sum([column9]) as 'column9'
,sum([column10]) as ' column10'
,sum([column11]) as 'column11'
,sum([column12]) as 'column12'
,sum([column13]) as 'column13'
,sum([column14]) as 'column14'
,sum([column15]) as 'column15'
,COUNT([column16]) as 'column16'
FROM dbo.Archive AS C
where (YearMonth BETWEEN 201101 AND 201104) AND
(
(ID like 'XXXXXX%' AND STATE = 'XX') OR
(ORG_NBR = 'XXXXXX' AND MEMBERSHIP_CODE = 'XXX')
)
group by
[YearMonth]
,[ID]
,[STATE]
,[ORG_NBR]
,[MEMBERSHIP_CODE]
,column6
,column7
,column8
[/CODE]
June 6, 2011 at 7:01 pm
Please provide sample data and table structures as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 7:08 pm
I don't feel comfortable doing that due to the nature of the data. I understand if you can't help without it. Thanks anyways!!
June 7, 2011 at 7:00 am
He said "sample" data. Feel free to make up fictional values to hide proprietary information so long as those values recreate the problem you are experiencing.
The reason we ask for complete queries, CREATE TABLE statements, and insert statements for sample data is it enables us to cut and paste into our own systems and reproduce your problem exactly. This saves us time in analyzing the problem and enables us to provide coded and tested solutions. If you will take the time to do this in the future, a lot more volunteers will be willing to try to solve your problem for you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 7, 2011 at 9:44 am
As Bob (Dixie) said, we don't need/want real data. It just needs to be something that recreates the issue you are seeing. The things we have done "should" have fixed it but it also seems like there is something else helping to cause the error.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 7, 2011 at 10:44 am
Here was the solution that worked in case somebody else runs into a problem like this. I think the issue was that the UPCs were in the Specialty_UPC table more than once in some cases:
select
[COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4]
,[COLUMN5]
,[COLUMN6]
,[COLUMN7]
,[COLUMN8]
,CASE WHEN ((count(distinct S.UPC)) > 0)
THEN 'Y'
ELSE 'N'
END AS Specialty_Ind
, /* Below this point should be red on excel sheet */
sum([COLUMN9]) as 'COLUMN9'
,sum([COLUMN10]) as ' COLUMN10'
,sum([COLUMN11]) as 'COLUMN11'
,sum([COLUMN12]) as 'COLUMN12'
,sum([COLUMN13]) as 'COLUMN13'
,sum([COLUMN14]) as 'COLUMN14'
,sum([COLUMN15]) as 'COLUMN15'
,COUNT([COLUMN16]) as 'COLUMN16'
FROM dbo.Archive AS C
LEFT JOIN (SELECT UPC FROM dbo.Specialty_UPCs GROUP BY UPC) AS S ON C.UPC = S.UPC
where (YearMonth BETWEEN 201101 AND 201104) AND
(
(ID like 'XXXXXX%' AND STATE = 'XX') OR
(ORG_NBR = 'XXXXXX' AND SOURCE_CODE = 'XXX')
)
group by
[COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4]
,[COLUMN5]
,[COLUMN6]
,[COLUMN7]
,[COLUMN8]
Thanks for your help everybody!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply