CODEWITHSUNDEEP
sqlms-access
Magda
Magda

Reputation: 23

SELECTING with multiple WHERE conditions on same table

I am currently working on my sql query that looks like this

SELECT reference.agent_ID, reference.policy_ID, Count(reference.status_ID) AS [Quoted Motor Breakdown] , Sum(IIf([status_ID]=1,1,0)) AS sold
FROM reference
GROUP BY reference.agent_ID, reference.policy_ID
HAVING (((reference.policy_ID)=1));

So results show as below

Agent| Number of policies quoted for Motor breakdown|number of policies Motor breakdown sold

I would like to add more columns to the same table showing results for remaining type of policies :

reference.policy_ID=2 (Travel), 
reference.policy_ID=3 (Home Insurance)  next to one agent

example: Mr Smith|Quoted Motor Breakdown| Sold| Quoted Motor Travel| Sold

I would also like to my query to show result=0 if there wos no quote for specific type of policy Any advise would be much appreciated Thank you

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you want conditional aggregation:

SELECT r.agent_ID,
       SUM(CASE WHEN r.policy_ID = 1 THEN 1 ELSE 0 END) AS [Quoted Motor Breakdown],
       SUM(CASE WHEN r.policy_ID = 1 AND status_ID = 1 THEN 1 ELSE 0 END) AS sold,
       SUM(CASE WHEN r.policy_ID = 2 THEN 1 ELSE 0 END) AS NumTravel,
       SUM(CASE WHEN r.policy_ID = 3 THEN 1 ELSE 0 END) AS NumHomeInsurance
FROM reference r
GROUP BY r.agent_ID;

If you are using MS Access, then you'll need to use the non-standard iif() instead:

SELECT r.agent_ID,
       SUM(iif(r.policy_ID = 1, 1, 0)) AS [Quoted Motor Breakdown],
       SUM(iif(r.policy_ID = 1 AND status_ID = 1, 1, 0)) AS sold,
       SUM(iif(r.policy_ID = 2, 1, 0)) AS NumTravel,
       SUM(iif(r.policy_ID = 3, 1, 0)) AS NumHomeInsurance
FROM reference as r
GROUP BY r.agent_ID;

Upvotes: 1

Related Questions