Beginner SQL help

I have extensive background programming in SAS.
I’m relatively new to SQL.
I need lots of help.
This is where I’ll post all of my requests for help.

I need a particular value for a particular field to be something else.

In the db I’m reading, I have a bunch of records where LOB = ‘X’. Whenever that happens I need it to be changed to LOB = ‘XX’. There are lots of other LOBs, of course. A, B, C, D, and so on all the way up to X, but whenever it’s ‘X’ I need it to be ‘XX’.

Here’s my code right now:

SELECT [VALUATION_DATE], [COMPANY], [LOB], Sum(PREMIUM) AS ‘PREMIUM’
FROM [SOURCE]
GROUP BY [VALUATION_DATE], [COMPANY], [LOB]
ORDER BY [VALUATION_DATE], [COMPANY], [LOB]

There must be some sort of WHEN clause I throw in there.

CASE
WHEN LOB = ‘X’
THEN ‘XX’
ELSE LOB
END
as NEWLOB

Aside: Looks better when indenting the “When” “then” “Else” lines, but this website ignores leading spaces.
Some people prefer this all on one line.
CASE WHEN LOB = ‘X’ THEN ‘XX’ ELSE LOB END as NEWLOB

You’d think that the word “IF” would be used, but “IF” has another use, for declaring variables, as opposed to creating new fields.
And, yes, you’ll have to repeat the “CASE WHEN” in the GROUP BY area, excluding the “AS NEWLOB” part.

1 Like

What the good Dr said.

Should add you can add as many “when… then…” statements as you’d like.

Yes. CASE WHEN…WHEN…WHEN…(yada yada yada)…ELSE … END as LOBSTERBISQUE

(Edited, for SV.)

Does SQL allow you to yada yada over sex?

1 Like

Thank you for all that.

[rant]I’ve always found this particularly annoying.[/rant]

1 Like

NEW QUESTION.

I have a WHERE clause in my statement:

SELECT... WHERE [COMPANY] = 'ABC' ...GROUP BY...ORDER BY...

I don’t need to see COMPANY in the summary report but if I don’t include it I’ll get some sort of 'COMPANY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause error.

In SAS I could DROP that variable. Is there any thing similar in SQL or am I stuck with that variable like I’m stuck with my in-laws?

You shouldn’t need to include Company in your Select clause just because it’s in the Where clause. The error message you posted is referring to Company already being in the Select clause, but not the Group by clause (or an aggregating function).

Remove it from your Select clause and see if that fixes it. Or maybe I’m missing something!

1 Like

I got halfway through typing the same thing when this popped up. Agree with BF at first glance.

Well, I need to tell it to only use records where [COMPANY] = ‘ABC’.
How would I do that without it being in the SELECT clause?

Where do I put the WHERE [COMPANY] = ‘ABC’?

Just to clarify, your Where clause is after your From clause, right?

SELECT [VALUATION_DATE], [COMPANY], [LOB], Sum(PREMIUM) AS ‘PREMIUM’
FROM [SOURCE]
WHERE [COMPANY] = 'X’
GROUP BY [VALUATION_DATE], [COMPANY], [LOB]
ORDER BY [VALUATION_DATE], [COMPANY], [LOB]

1 Like

Yes.

Do I get rid of it from the Select clause & the others and just leave it after FROM ?

Yes, that is what I should do.

Thanks, everybody.

If by “it” you mean “Company”, then yes. Remove that from everything except your Where clause.

:tup:

Allow me to vent. This is stupid.

I only want the left 6 characters of the valuation date, so in the SELECT clause I put left([VALUATION_DATE],6) as [VALDATE]…but in the GROUP BY & ORDER BY clauses I still need to put [VALUATION_DATE].

That’s ludicrous! Why do I have to rename the thing in the SELECT clause if I don’t even use that name in the GROUP & ORDER BY clauses? (That’s more of a rhetorical question, but if you have an answer for it, I’ll listen.)

Simple rule: if some field (new or existing) is in the SELECT, it needs to be in the Group BY, if you are summing or doing any math function to a field.
Personally, I’d add “left([VALUATION_DATE],6)” to the Group By clause.
Otherwise you will get multiple instances of “left([VALUATION_DATE],6)” in your result, one for each separate instance where “left([VALUATION_DATE],6)” is the same, but the rest of [VALUATION_DATE] is different.

1 Like

:iatp:

eta: actually approve of both posts. I also use SAS more frequently, and also am very irritated that the alias can’t be used in a subsequent order/group by statement. And also always forget this fact the first time.

I understand all of that, but why do I need to rename it (e.g., “[VALDATE]”) in the SELECT statement if I never, ever use that new name again? I need to use the original variable name of [VALUATION_DATE] in the GROUP & ORDER BY clauses.