CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DooDoo
DooDoo

Reputation: 13447

How to use GROUP BY to calculate intervals

please consider this image:

enter image description here

I have a table like this:

 Age              Active            Men/Women
 -------------------------------------------------

I want to write a query that calulate Count of every age intervals for men and women.I don't know How I can use GROUP BY and intervals.

in my reports in left side of above image I want to calculate UnActive rows.How I can merge this to query to one query?

thanks

Upvotes: 1

Views: 371

Answers (5)

gbn
gbn

Reputation: 432271

SELECT
    Active.State,
    Age.Base, Age.Base+4,
    COUNT(*) AS TotalCount,
    COUNT(CASE WHEN T.[Men/Women] = 'man' THEN 1 END) AS ManCount,
    COUNT(CASE WHEN T.[Men/Women] = 'woman' THEN 1 END) AS WomanCount
FROM
    (
    VALUES (10),(15),(20),(25),(30),(35),(40) /*.. add the rest */,(90),(95)
    ) AS Age(base)
    CROSS JOIN
    (
    VALUES (0), (1)
    ) AS Active(State)
    LEFT JOIN
    MyTable T ON T.Age BETWEEN Age.Base AND Age.Base+4 AND T.Active = Active.State
GROUP BY
    Active.State,
    Age.Base, Age.Base+4;

After this, you can format it how you want

Edited to generate empty ranges

Upvotes: 2

Seph
Seph

Reputation: 8703

As others have mentioned this is trying to report on a way that is not the same as your data at all.

But I think the best way achieve this within SQL is to create a scalar function (props to @gbn for the min/max age range logic):

CREATE FUNCTION usvf_calculate_age_bracket
(
    -- Add the parameters for the function here
    @age INT
)
RETURNS VARCHAR(50)
AS
BEGIN
    -- Return the result of the function
    RETURN CAST(@age/5*5 AS VARCHAR(20)) + '-' + CAST(@age/5*5+4 AS VARCHAR(20))
END
GO

Then in your query you can do:

SELECT SUM(CASE WHEN gender = 'F' THEN active ELSE 0 END) AS [Women],
    SUM(CASE WHEN gender = 'M' THEN active ELSE 0 END) AS [Men], 
    SUM(CAST(active AS int)) AS [Total],
    'Age ' + dbo.usvf_calculate_age_bracket(age) AS [Age]
FROM myTable
GROUP BY dbo.usvf_calculate_age_bracket(age)

My test code:

DECLARE @T TABLE
(
age int,
active bit,
gender char(1)

)

INSERT INTO @T VALUES (25, 1, 'M'),(32, 0, 'F'),(21, 1, 'M'),(22, 1, 'F'),(28, 1, 'F'),(32, 0, 'M'), (23, 1, 'M'),(42, 0, 'F'),(29, 1, 'M'),(29, 1, 'F'),(28, 1, 'F'),(32, 1, 'M')

SELECT SUM(CASE WHEN gender = 'F' THEN active ELSE 0 END) AS [Women],
SUM(CASE WHEN gender = 'M' THEN active ELSE 0 END) AS [Men], 
SUM(CAST(active AS int)) AS [Total],
'Age ' + dbo.usvf_calculate_age_bracket(age) AS [Age] FROM @T
GROUP BY dbo.usvf_calculate_age_bracket(age)

Results:

Women Men Total Age
1    2    3     Age 20-24
3    2    5     Age 25-29
0    1    1     Age 30-34
0    0    0     Age 40-44

Which seems to be close to what you want, and it isn't an overly complicated SQL query to achieve this. (except I can't work out your 'total' column)

Upvotes: 1

DRapp
DRapp

Reputation: 48139

You can actually expand on from what Andrea provided...

SELECT
    CASE WHEN YT.AGE BETWEEN 15 AND 19 THEN 'Age 15-19'
         WHEN YT.AGE BETWEEN 20 AND 24 THEN 'Age 20-24'
         ELSE 'Other'
    END AGE_INTERVAL,
    SUM( CASE WHEN YT.Active THEN 1 ELSE 0 END ) as ActiveCount,
    SUM( CASE WHEN YT.Gender = 'M' THEN 1 ELSE 0 END ) as MaleCount,
    SUM( CASE WHEN YT.Gender = 'F' THEN 1 ELSE 0 END ) as FemaleCount,
    COUNT(*) AgeGroupCount
FROM
    YourTable YT
GROUP BY
   Age_Interval

Upvotes: 1

Wim
Wim

Reputation: 1096

This should work:

SELECT SUM(CASE WHEN [Men/Women] = 'M' THEN 1 ELSE 0 END) AS Men,
 SUM(CASE WHEN [Men/Women] = 'W' THEN 1 ELSE 0 END) AS Women,
 COUNT(*) as Total,
 CASE WHEN Age BETWEEN 10 AND 14 THEN 'Age 10-14'
    WHEN Age BETWEEN 15 AND 19 THEN 'Age 15-19'
    WHEN Age BETWEEN 20 AND 24 THEN 'Age 20-24'
    ELSE 'Old'
END Age
FROM MyTable
WHERE Active = 1
GROUP BY     CASE WHEN Age BETWEEN 10 AND 14 THEN 'Age 10-14'
    WHEN Age BETWEEN 15 AND 19 THEN 'Age 15-19'
    WHEN Age BETWEEN 20 AND 24 THEN 'Age 20-24'
    ELSE 'Old'
END

Upvotes: 1

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

You can GROUP BY on age interval CASEs:

SELECT
    COUNT(*) AGE_COUNT,
    CASE WHEN AGE BETWEEN 15 AND 19 THEN 'Age 15-19'
         WHEN AGE BETWEEN 20 AND 24 THEN 'Age 20-24'
         ELSE 'Other'
    END AGE_INTERVAL
FROM
    YOUR_TABLE
GROUP BY
    CASE WHEN AGE BETWEEN 15 AND 19 THEN 'Age 15-19'
         WHEN AGE BETWEEN 20 AND 24 THEN 'Age 20-24'
         ELSE 'Other'
    END

Upvotes: 1

Related Questions