CODEWITHSUNDEEP
phpmysqlsqlperformancequery-optimization
GSinghLDN
GSinghLDN

Reputation: 181

Have a select query with multiple where clauses return multiple result rows in one result table?

I have a query such as:

SELECT COUNT(id) AS user_list FROM tbl_users
WHERE (enrolled = 1 AND age >=15 AND age < 20)
OR (enrolled = 1 AND age >=20 AND age < 25)
OR (enrolled = 1 AND age >=25 AND age < 30)
OR (enrolled = 1 AND age >=30)

How can I have it return a result for each where condition in one row and one table as such:

user_list
---------
   18    --(enrolled = 1 AND age >=15 AND age < 20)
   12    --(enrolled = 1 AND age >=20 AND age < 25)
   22    --(enrolled = 1 AND age >=25 AND age < 30) 
   56    --(enrolled = 1 AND age >=30)

Upvotes: 2

Views: 927

Answers (6)

GarethD
GarethD

Reputation: 69769

As has been mentioned you can use a case and perform this all in one query, however you don't need to use a subquery, although I am not a massive fan of it, MySQL allows you to use column aliases in the group by, so the following will work:

SELECT  CASE WHEN (enrolled = 1 AND age >=15 AND age < 20) THEN '15-19'
            WHEN (enrolled = 1 AND age >=20 AND age < 25) THEN '20-24'
            WHEN (enrolled = 1 AND age >=25 AND age < 30) THEN '25-29'
            WHEN (enrolled = 1 AND age >=30) THEN '30+'
            ELSE 'Other'
        END AS AgeRange 
        COUNT(id) AS user_list 
FROM    tbl_users
WHERE   Enrolled = 1
AND     Age >= 15
GROUP BY AgeRange;

Note, I have still added a where clause to avoid scanning redunant data.

Example on SQL Fiddle

This falls down however if you have missing data, e.g. an empty table will return, not this:

AGERANGE    USER_LIST
15-19       0
20-24       0
25-29       0
30+         0

As you might expect. To get around this I would create a pseudo table to select from, and left join your data:

SELECT  t.Name AS Age_Range,
        COUNT(u.ID) AS User_List
FROM    (   SELECT  '15-19' AS Name, 15 AS LowerBound, 20 AS UpperBound, 1 AS Enrolled
            UNION ALL
            SELECT  '20-24' AS Name, 20 AS LowerBound, 25 AS UpperBound, 1 AS Enrolled
            UNION ALL
            SELECT  '25-29' AS Name, 25 AS LowerBound, 30 AS UpperBound, 1 AS Enrolled
            UNION ALL
            SELECT  '30+' AS Name, 30 AS LowerBound, 9999999 AS UpperBound, 1 AS Enrolled
        ) t
        LEFT JOIN tbl_Users u
            ON u.Enrolled = t.enrolled
            AND u.Age >= t.LowerBound
            AND u.Age < t.UpperBound
GROUP BY t.Name;

Example on SQL Fiddle

Upvotes: 2

Grigory
Grigory

Reputation: 1

this is simple

select count(1),ceil(age/5)*5 from tbl_users where enrolled=1 group by ceil(age/5)

Upvotes: 0

Sadek Noureddine
Sadek Noureddine

Reputation: 577

There are the following SQL clauses that could help:

Group by, Case, and Subqueries...look them up and play with that, should be fun to figure it out yourself...

You could look into having another column in your query (e.g. case between 15 and 18 then 1, case ... then 2, ...) as Range, identifying a range and grouping by that...

This is an example of how to identify your ranges as columns in a subquery, and query on top of that grouped by the ranges in your subquery:

select count(*) as Count, r as Range 
    from 
        (select 
        (case 
        when enrolled = 1 AND age >=20 AND age < 25 then 'r1' 
        when enrolled = 1 AND age >=25 AND age < 30 then 'r2' 
        end) as r 
        from Text)          
    as groups 
group by r

This results in the following:

Range | Count

r1 | 10

r2 | 7

where r1 can be your range corresponding to "enrolled = 1 AND age >=20 AND age < 25" or so on, defined in the case clause.

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this method

create table t (list1 int,list2 int,list3 int,list4 int)

insert into t
SELECT 
SUM(case when age >=15 AND age < 20 then 1 else 0 end) AS user_list1,
SUM(case when age >=20 AND age < 25 then 1 else 0 end) AS user_list2,
SUM(case when age >=25 AND age < 30 then 1 else 0 end) AS user_list3,
SUM(case when age >=15 AND age < 20 then 1 else 0 end) AS user_list4
FROM tbl_users
WHERE enrolled = 1 ;

select list1 from t union all
select list2 from t union all
select list3 from t union all
select list4 from t

Upvotes: 1

Jokerius
Jokerius

Reputation: 1320

There should not be problem for performance to use 4 simple queries instead of one complicated

Upvotes: 0

wxyz
wxyz

Reputation: 707

U could use unions:

SELECT COUNT(id) AS user_list
  FROM tbl_users
 WHERE (enrolled = 1 AND age >= 15 AND age < 20)
union
SELECT COUNT(id)
  FROM tbl_users
 where (enrolled = 1 AND age >= 20 AND age < 25)
union
SELECT COUNT(id)
  FROM tbl_users
 where (enrolled = 1 AND age >= 25 AND age < 30)
union
SELECT COUNT(id) FROM tbl_users where (enrolled = 1 AND age >= 30)

Upvotes: 0

Related Questions