CODEWITHSUNDEEP
sql
user1352864
user1352864

Reputation: 11

SQL Case when condition more than

I have product and agegroup table.

I'm joining these two tables on match of id from agegroup table and agegroup from the product table.

The query I'm using is here;

WITH CTE_AgeGroup AS                                                                            
                (
                SELECT  p.agegroup,
                      CASE
                             WHEN AgeFromMonths >=0 AND AgeToMonths <= 24 
                               THEN
                                    CASE 
                                          WHEN AgeFromMonths >=0 AND AgeToMonths <= 3 THEN '0-3 Months'
                                          WHEN AgeFromMonths >=3 AND AgeToMonths <= 6 THEN '3-6 Months'
                                          WHEN AgeFromMonths >=6 AND AgeToMonths <= 9 THEN '6-9 Months'
                                          WHEN AgeFromMonths >=9 AND AgeToMonths <= 12 THEN '9-12 Months'
                                          WHEN AgeFromMonths >=12 AND AgeToMonths <= 18 THEN '12-18 Months'
                                          WHEN AgeFromMonths >=18 AND AgeToMonths <= 24 THEN '18-24 Months'
                                          END  
                           END as  'age'
                FROM agegroup a inner join product p on
                a.id= p.agegroup

                ),
                  CTE_AgeGroupResultSet AS
                (
                   select
                        Age, Count(*) AS CountAge from CTE_AgeGroup  group by Age   
                      )
                select * from CTE_AgeGroupResultSet order by age;

However, my problem is that if product A is listed in '0-3 months', and product B is listed om '3- 6 months', these two products are not get listed in '0-24 months'.

I want modify my query so that Product A and B are also get listed in '0-24 months'

Upvotes: 0

Views: 684

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

You might use with rollup in group by to get grand count:

WITH CTE_AgeGroup AS
(
    SELECT p.agegroup,
           CASE WHEN AgeFromMonths >=0 AND AgeToMonths <= 24 
                THEN CASE WHEN AgeFromMonths >=0 AND AgeToMonths <= 3 
                          THEN '0-3 Months'
                          WHEN AgeFromMonths >=3 AND AgeToMonths <= 6 
                          THEN '3-6 Months'
                          WHEN AgeFromMonths >=6 AND AgeToMonths <= 9 
                          THEN '6-9 Months'
                          WHEN AgeFromMonths >=9 AND AgeToMonths <= 12 
                          THEN '9-12 Months'
                          WHEN AgeFromMonths >=12 AND AgeToMonths <= 18 
                          THEN '12-18 Months'
                          WHEN AgeFromMonths >=18 AND AgeToMonths <= 24 
                          THEN '18-24 Months'
                      END  
            END as 'age'
    FROM agegroup a 
         inner join product p 
            on a.id= p.agegroup
),
CTE_AgeGroupResultSet AS
(
    select case when grouping (Age) = 1 then '0-24' else Age end Age, 
           Count(*) AS CountAge 
      from CTE_AgeGroup
     group by Age WITH ROLLUP
)
select * 
  from CTE_AgeGroupResultSet 
 order by age;

Also, if you add a varchar column to agegroup containing group description you will eliminate case altogether.

Upvotes: 1

Vimalnath
Vimalnath

Reputation: 6463

I am not sure why would you go for this case functions. It would be better to use UNION operator(this just takes the union of both your product and agegroup tables)

FULL OUTER JOIN(joins both the tables and takes data from both tables even if there are NULL values in both tables.)

Upvotes: 0

Related Questions