How to implement sequence in the SQL query

Question

I have a query in SQL Server which gives me the count of policies according to AGE and SEX as mentioned below.

SELECT 
    PLYMMRAGE as AGE,
    MMRSEX as SEX,
    COUNT(PLYNO) AS POLICYCOUNT
FROM 
    ADMGMPLYMSR 
GROUP BY
    PLYMMRAGE, MMRSEX
ORDER BY 
    MMRSEX DESC, PLYMMRAGE

Output of this query is:

AGE   SEX    POLICYCOUNT
------------------------
2     M      10
4     M       9
5     M       6
8     M       0
1     F       4
2     F       6
4     F       0

But I want that even if the age is not present the row should display for all ages with policy count as 0 till age 10.

    AGE   SEX    POLICYCOUNT
   -------------------------
    1     M       0
    2     M      10
    3     M       0      
    4     M       9
    5     M       6
    6     M       0
    7     M       0
    8     M       0
    9     M       0
    10    M       0
    1     F       4
    2     F       6
    4     F       0

and so on.

How can I insert the data with zero count if the age for that row is not present? Even if using procedure is fine

Hi i think you can looks to a CTE sql server , what is your’re sql server version please ? — Sanpas
– Sanpas, Commented Apr 4, 2019 at 5:14

Barbaros Özhan · Accepted Answer · 2019-04-04 05:31:27Z

2

You can use a LEFT JOIN and coalesce together

with ADMGMPLYMSR( PLYMMRAGE, MMRSEX, PLYNO ) as
(
 select 2,'M',10 union all
 select 4,'M', 9 union all
 select 5,'M', 6 union all
 select 8,'M', 0 union all     
 select 1,'M', 4 union all
 select 2,'M', 6 union all
 select 4,'M', 0     
), t AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 FROM t WHERE n+1<=10
)
SELECT COALESCE(PLYMMRAGE,n) as AGE, COALESCE(MMRSEX,'M') as SEX, 
       COUNT(PLYNO) AS POLICYCOUNT
  FROM t 
  LEFT JOIN ADMGMPLYMSR ON PLYMMRAGE = n
 GROUP BY coalesce(PLYMMRAGE,n), MMRSEX
 ORDER BY coalesce(MMRSEX,'M') DESC, coalesce(PLYMMRAGE,n);

Demo

edited Apr 4, 2019 at 5:31

answered Apr 4, 2019 at 5:16

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashutosh Over a year ago

Hi Barbaros, please can you explain the starting part ''with ADMGMPLYMSR( PLYMMRAGE, MMRSEX, PLYNO ) as ( select 2,'M',10 union all select 4,'M', 9 union all select 5,'M', 6 union all select 8,'M', 0 union all select 1,'M', 4 union all select 2,'M', 6 union all select 4,'M', 0 ), t AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM t WHERE n+1<=10 )" sorry bit new to sql server. Why you have hardcoded the result ? what should i mention there instead ?

Barbaros Özhan Over a year ago

@Ashutosh there's no such certain mechanism for new row generation is sql-server like in oracle or postgres. Since we need to add such a query, which generates the values of table "t" , above.

Collectives™ on Stack Overflow

How to implement sequence in the SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related