CODEWITHSUNDEEP
sql
user2961927
user2961927

Reputation: 1718

Group by in sql

I am a SQL rookie. I am writing a query:

SELECT iif([Cancellation Date] is NULL, 'Not Cancelled', 'Cancelled') as Status,
       SUM(Loss) as [Total Loss]
from INSURANCE_DATA
GROUP by Status;

So basically I want to aggregate the losses by the policy status. This query is unable to run, because I am grouping by a new variable generated in this very query?

I can write another query based on this one to have what I want, but is there any intriguing solution enabling me to get it done in one query?

Upvotes: 0

Views: 97

Answers (2)

isick
isick

Reputation: 1487

You're right, you can't use the alias in the group by. But if you make your group by value...

GROUP BY [Cancellation Date] is NULL

Then it's effectively the same thing.

Upvotes: 1

Daniel Steel
Daniel Steel

Reputation: 101

Have you tried grouping by the expression you are declaring in the select rather than referring to it's alias?

e.g.

SELECT iif([Cancellation Date] is NULL, 'Not Cancelled', 'Cancelled') as Status,
SUM(Loss) as [Total Loss] from INSURANCE_DATA GROUP BY iif([Cancellation Date] is NULL,
'Not Cancelled', 'Cancelled');

Dan

Upvotes: 1

Related Questions