Help with sql select

[morfasie]

I have a table like this

Nr | ID | Agent Code |Premium | Plan
-----------------------------------------------------------
345 | 8006194565432 | WW040001 | 60 | Pensioner Plan |
348 | 8006194565432 | WW040001 | 40 | Dependent Adult|
323 | 8006194565432 | WW040001 | 30 | Pensioner Plan |
333 | 1401014565081 | WW040001 | 40 | Pensioner Plan |
-----------------------------------------------------------

I want to sum the premium for the agent WW040001, but it has to count the 3 pensioner plans as 3 plans and the dependent plan as 1 plan.

So the output should be
Agent Code | Count Plans | Total Premium
------------------------
WW040001 | 3 | 170 |
------------------------

How do I do it? It has to count the 345 and 348 as one policy because of the same ID for the different policies.

 

[JarlH]

SELECT agent_code,
SUM(CASE plan WHEN 'Pensioner Plan' THEN 1
ELSE 0 END) AS count_plans,
SUM(premium) AS total_premuim
FROM plan_table
WHERE agent_code = 'WW040001'
GROUP BY agent_code

 

[morfasie]

Hi thanks for the great reply, how do I add another check for lets say Independent Family Care, where do I put the case then?

 

[PHV]

Something like this ?
SELECT agent_code,
SUM(CASE plan WHEN 'Pensioner Plan' THEN 1
ELSE 0 END) AS Pensioner_plans,
SUM(CASE plan WHEN 'Dependent Adult' THEN 1
ELSE 0 END) AS Dependent_plans,
SUM(CASE plan WHEN 'Independent Family Care' THEN 1
ELSE 0 END) AS Independent_plans,
SUM(premium) AS total_premuim
FROM plan_table
WHERE agent_code = 'WW040001'
GROUP BY agent_code

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[morfasie]

Thanks but I want to sum all of them into one field like count_premiums

 

[PHV]

Ah, simply this ?[tt]
SELECT agent_code,
COUNT(*) AS count_plans,
SUM(premium) AS total_premium
FROM plan_table
WHERE agent_code = 'WW040001'
GROUP BY agent_code[/tt]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[watergrinder]

Is your tables properly normalized?