Looping through another table and using values as variables for SELECT query

Question

Sample data from the Delays Table is shown below:

+----------+-------------+
| Customer | DaysDelayed |
+----------+-------------+
|   698125 |          13 |
|   698126 |          31 |
|   698127 |           2 |
|   698128 |          11 |
|   698129 |           5 |
|   698130 |           4 |
|   698131 |          42 |
|   698132 |          29 |
|   698133 |          29 |
+----------+-------------+

The output desired is:

+------------+-------+
|   Range    | Count |
+------------+-------+
| 0-7 days   |     3 |
| 8-20 days  |     2 |
| 21-30 days |     2 |
| 31-45 days |     2 |
+------------+-------+

For getting the output I have written 4 queries:

SELECT '0-7 days', Count(*) 
FROM Delays 
WHERE DaysDelayed between 0 AND 7

SELECT '8-20 days', Count(*) 
FROM Delays 
WHERE DaysDelayed between 8 AND 20

and so on (the values for the label and the WHERE condition changing in each query).

The day ranges change from time to time and also the number of ranges can increase/decrease.

From a maintainability perspective, I want to store the Range values in a table as follows:

+---------+------------+----------+----------+
| RangeID | RangeName  | LowerEnd | UpperEnd |
+---------+------------+----------+----------+
|       1 | 0-7 days   |        0 |        7 |
|       2 | 8-20 days  |        8 |       20 |
|       3 | 21-30 days |       21 |       30 |
|       4 | 31-45 days |       31 |       45 |
+---------+------------+----------+----------+

and loop through this table with one SELECT query (instead of 4 queries at present).

Please help.

Adriaan Stander · Accepted Answer · 2013-05-27 04:55:04Z

1

Have you tried something like

SELECT  RangeName,
        COUNT(d.Customer) Cnt
FROM    Ranges r LEFT JOIN
        Delays d    ON  d.DaysDelayed BETWEEN r.LowerEnd AND r.UpperEnd
GROUP BY RangeName

SQL Fiddle DEMO

answered May 27, 2013 at 4:55

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

Comments

diegoperini · Accepted Answer · 2013-05-27 04:54:03Z

0

Use UNION operator. It merges rows of any number of given tables

Here is the documentation: http://dev.mysql.com/doc/refman/5.0/en/union.html

answered May 27, 2013 at 4:54

diegoperini

1,80622 silver badges37 bronze badges

Collectives™ on Stack Overflow

Looping through another table and using values as variables for SELECT query

2 Answers 2

SQL Fiddle DEMO

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related