Product is probably a reserved word (likely a multiplication thing). You could put it in brackets to use it as a name. You could also mispell it like Prdct
1 Like
Yeah, probably. Some SQL’s require it, some don’t.
You can get around it by nesting SELECT statements.
2 Likes
Or PROD (heh-heh).
If it’s just one product and one set of Plan Codes, then a GROUP BY isn’t necessary. This would work just fine
SELECT ‘UL’ AS [Product],
SUM(StatReserve) as StatReserve
FROM [db]
WHERE [Company] = ‘ABC’
AND [PlanCode] IN (‘123’,‘456’,‘789’)
2 Likes
Am I the only one that, upon realizing that there’s a missing comma, sings “comma comma comma comma comma chameleon”?
3 Likes
“do you wanna hurt me?”
1 Like
How to say you’re old without saying you’re old.
1 Like
Fixed.
3 Likes
iswydt. :tup: 
IFY(IFYP)OC
3 Likes
New question:
Data:
| LOB | Policy | Unit |
|---|---|---|
| prop | 1 | 1 |
| prop | 1 | 2 |
| auto | 2 | 1 |
| auto | 2 | 2 |
I want a summary query that has a ‘count’ of distinct Policy for prop, and distinct Policy/Unit for auto. Is there a better way to do this than two separate queries and append them together basically? So I want this:
| LOB | Count |
|---|---|
| prop | 1 |
| auto | 2 |
SELECT LOB,
COUNT(DISTINCT CASE
WHEN LOB = ‘prop’ THEN Policy
WHEN LOB = ‘auto’ THEN Unit END) AS thecount
FROM table
GROUP BY LOB
Got it…I forgot ‘case when’ was a thing. (I rarely use SQL).
Edit:
Can’t just use Unit for auto…needs to be by distinct policy when prop and by distinct policy/unit combo for auto. Above doesn’t work if two policies both have units numbered 1 & 2.
So perhaps a combo field that uses the appropriate parts from each, then dosticnt on the combination?
Makes sense. I’m pulling from a database I can’t edit though. Can you query against a concatenated column in the same step? Feels like nesting is required in SQL world.
Select
…
case when…
from
create table new as…
select *, policy||unit as newvar
…something like that???
Try using Common Table Expressions to do the ‘nesting’. That seems a little more like traditional programming. Give me a little time and i can come up with some code.
I asked GPT because I’ve become that guy.
SELECT LOB,
COUNT(DISTINCT Policy) AS Count
FROM InsuranceData
WHERE LOB = 'prop'
GROUP BY LOB
UNION ALL
SELECT LOB,
COUNT(DISTINCT CONCAT(Policy, '-', Unit)) AS Count
FROM InsuranceData
WHERE LOB = 'auto'
GROUP BY LOB;
And the way this works is:
Select …
from (Select …
from …
where…)
where …
;
Though iirc steve has a less matryoshkas’ way of doing it.
I can’t ask ChatGPT because work has banned it. You can use the UNION approach, which is simple. The without UNION using CTEs approach would look something like this (assuming [Policy] is a char or varchar, and [Unit] is a numeric of some kind):
WITH DTable (LOB, PolUnit) AS
(SELECT LOB,
CASE WHEN LOB = ‘auto’ THEN [Policy] + CAST([Unit]AS VARCHAR(20))
ELSE [Policy] END
FROM SourceTable
GROUP BY LOB,
CASE WHEN LOB = ‘auto’ THEN [Policy] + CAST([Unit] AS VARCHAR(20))
ELSE [Policy] END)
SELECT LOB, COUNT(PolUnit) FROM DTable
GROUP BY LOB
ORDER BY LOB