Using GROUP BY in a SELECT statement with Multi-CASE
-
I have been working on this code for a couple hours now, so please excuse the possible typos. The only way I could find to handle a MULTI case statement was to write two case statements. I need to check the values in app and UserID. I got that to work with this code:
SELECT [Start Date], SUM(Volume),
CASE
WHEN Application = 'APP1' THEN
CASE UserID
WHEN '1' THEN 'P'
WHEN '2' THEN 'R'
WHEN '3' THEN 'R'
ELSE 'UNKNOWN'
END
ELSE
CASE
WHEN Application <> 'APP1' THEN 'V'
ELSE 'UNKNOWN'
END
END AS 'DType'
FROM ...
Now the issue is, I want to add the following syntax GROUP BY [Start Date], 'DType'... but it is stating that USERID and APP1 are not part of the aggregate. Yet those values are not returned the recordset.
Any help would be appreciated... this is fustrating me, and seems simple.
"Life without progression is entropy"
Sam Jaynes -
If you have an expression generating a column in the resultset, and you need to GROUP BY the expression, you need to replicate all the code for the expression.
An alternative is to embed the expression in a derived table, and perform the aggregation in the outer SELECT:
SELECT [Start Date], DType, Sum(Volume)
FROM
--Create a derived table that handles the DType expression
(
SELECT
[StartDate],
Volume,
CASE UserID
WHEN '1' THEN 'P'
-- etc etc
END As DType
FROM YourTable
WHERE ...
) dt
GROUP BY [StartDate], DType
-
Thank you for the quick response... I tried that and in my WHERE clause I am using an IN statement which uses ('V1',V2','V3') etc... and SQL doesn't like the double paren at the end... any work arounds?
"Life without progression is entropy"
Sam Jaynes -
You are trying to hardcode business rules. What must be done UNDER NO CIRCUMSTANCES.
You must create table User_DType:
CREATE TABLE User_DType (
UserId int,
DType nvarchar(30)
)
INSERT INTO User_DType (UserId, DType)
SELECT 1, 'P'
UNION
SELECT 2, 'R'
UNION
SELECT 3, 'R'
SELECT ..., ISNULL(UT.DType, 'UNKNOWN')
FROM ...
LEFT JOIN User_DType UT ON UT.UserId = [main table].UserId
_____________
Code for TallyGenerator -
Ahhh... I forgot to name the derived table (DT)
Thanks...
"Life without progression is entropy"
Sam Jaynes -
May of jump the gun a little bit... the subquery usually took 1min 10sec to run... I added the outside select statement and the group by statements and finally killed the query after 4 minutes of not receiving anything.
So, I added the DT after the subquery... do I need to create a table, or add a # sign before it. I haven't worked with derived or temp tables often.
"Life without progression is entropy"
Sam Jaynes -
Serigy,
Unfortunately, I only have read rights to this server and I did not see the reason to have a table create when there will only be the three values V1, V2, and V3. I know you stated UNDER NO CIRCUMSTANCES, but logically, can I work around it?
"Life without progression is entropy"
Sam Jaynes -
If you have only read rights how can you create or alter SP?
If you have rights to run ad-hoc queries only (I better keep silence about such "smart" sequrity model, just not to be rude) you may have this look-up table in your query:
INNER JOIN (select 1 as UserId, 'P' as DType UNION SELECT .... ) UT ON UT.UserId = ...
_____________
Code for TallyGenerator -
Why don't you use this one
CASE
WHEN Application+UserID = 'APP11' THEN 'P'
WHEN Application+UserID in 'APP12' THEN 'R'
WHEN Application+UserID in 'APP13' THEN 'R'
ELSE 'UNKNOWN'
If UserID is a number you need to convert the number in varchar like this
ltrim(rtrim(str(UserID ))) to avoid extra spaces
The general idea is to user a string concatenation insead to test single values.
Hope this help.
Claudia
-
I reformated your code to be easier to understand, and this is what I got
SELECT [Start Date],
SUM(Volume),
CASE
WHEN Application = 'APP1' THEN
CASE UserID
WHEN '1' THEN 'P'
WHEN '2' THEN 'R'
WHEN '3' THEN 'R'
ELSE 'UNKNOWN' -- No need for the ELSE here. Could be taken care of later.
END
ELSE
CASE
WHEN Application <> 'APP1' THEN 'V' -- Redundant since Application already is <> 'App1'.
ELSE 'UNKNOWN' -- The only reason for this case to occur is when Application is NULL. True?
END
END AS 'DType'
FROM ...
There is a lot of redundant code, so this is my suggestion to your problem.
SELECT z.[Start Date],
z.DType,
SUM(z.Volume)
FROM (
SELECT [Start Date],
Volume,
CASE
WHEN Application = 'APP1' AND UserID = '1' THEN 'P'
WHEN Application = 'APP1' AND UserID = '2' THEN 'R'
WHEN Application = 'APP1' AND UserID = '3' THEN 'R'
WHEN Application <> 'APP1' THEN 'V'
ELSE 'UNKNOWN'
END DType
FROM ...
) z
GROUP BY z.[Start Date],
z.DType
ORDER BY z.[Start Date],
z.DType
N 56°04'39.16"
E 12°55'05.25" -
This will potentially lead to wrong results. What when Application + UserID is 'App131'?
Is that Application 13 and User 1, or Application 1 and User 31?
N 56°04'39.16"
E 12°55'05.25" -
We can shorten the code still further. But I'm just riding Peso's suggestion...
SELECT z.[Start Date],
z.DType,
SUM(z.Volume)
FROM (
SELECT [Start Date],
Volume,
CASE
WHEN Application = 'APP1' AND UserID = '1' THEN 'P'
WHEN Application = 'APP1' AND UserID IN ('2', '3') THEN 'R'
WHEN Application <> 'APP1' THEN 'V'
ELSE 'UNKNOWN'
END DType
FROM ...
) z
GROUP BY z.[Start Date],
z.DType
ORDER BY z.[Start Date],
z.DType
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
A compromise between
1. a lookup table with app,user,dtype (which would involve some odd joins anyway with the <> and the ELSE) and
2. just putting a slab of code in your proc (which means information is buried where you can't find it, and you effectively have denormalised 'data' since the same (or what should be the same!) mapping may be specified in more than one place)
would be to put this CASE statement in a scalar UDF. That way you can clearly identify the purpose of the code, you can find it when you need to, and you can change it in just one place when necessary (which it will be, given time), rather than trawling through every procedure looking for this bit of logic. Still, the actual data mappings should still preferably be in a table, as you might need to report on them - or on time-dependent versions of them, etc...(i.e. they are data and should be in a database).
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Peter,
"Life without progression is entropy"
Sam Jaynes - >SQL doesn't like the double paren at the end...that isn't the correct explanation of your error. SQL doesn't have a problem with double parentheses.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply