CODEWITHSUNDEEP
sqlsql-server
Skiltz
Skiltz

Reputation: 554

SQL Group By Range

We have two columns First_Issue and Expiry_Issue

If I want to find out how many subscribers we had for issue 1 I would do a query like:

select COUNT(*) from Subscriber 
where First_issue <= 1 and Expiry_issue >= 1

Now I want to write a query that shows me the number of subscribers we had for every issue, not exactly sure where to start, any pointers appreciated.

Upvotes: 0

Views: 89

Answers (1)

Bulat
Bulat

Reputation: 6979

If you don't have all issue numbers available in issues table you can generate them like this: 

WITH Issues AS (
  SELECT 1 as Number
  UNION ALL
  SELECT Number + 1 as Number
  FROM Issues WHERE Number + 1 <= (SELECT MAX(Expiry_Issue) FROM Subscriber)
)
SELECT issue.Number, COUNT(DISTINCT s.id)
FROM 
  Subscriber s INNER JOIN 
  Issues i ON i.Number BETWEEN s.First_Issue AND s.Expiry_Issue

Upvotes: 1

Related Questions