CODEWITHSUNDEEP
mysqldatabase
user391986
user391986

Reputation: 30906

mysql loop select

I have an output to a mysql command that outputs

+------------+---------------+
| WeekNumber | Count         |
+------------+---------------+
|          5 |             1 |
|         8  |             2 |
|         34 |             0 |
+------------+---------------+

SELECT week, count 
FROM mytable;

How can I modify the query so that I can provide a week range (2 - 35) and have the count = 0 if no results matches otherwise show the count above.

Upvotes: 1

Views: 2272

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

This may be a weird amount of typing but will do it for you

select a.WeekNumber,ifnull(b.Count,0) Count
from (select * from 
(select 1 WeekNumber union select 2 union select 3 union select 4 union select 5
union select  6 union select  7 union select  8 union select  9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 20
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
union select 31 union select 32 union select 33 union select 34 union select 35
union select 36 union select 37 union select 38 union select 39 union select 40
union select 41 union select 42 union select 43 union select 44 union select 45
union select 46 union select 47 union select 48 union select 39 union select 50
union select 51 union select 52 union select 53) aa) a
left join mytable b using (WeekNumber)
where WeekNumber between 2 and 35;

Here is some sample data

mysql> drop database if exists user391986;
Query OK, 1 row affected (0.03 sec)

mysql> create database user391986;
Query OK, 1 row affected (0.01 sec)

mysql> use user391986
Database changed
mysql> CREATE TABLE mytable
    -> (WeekNumber int,Count int,primary key(WeekNumber));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable values (5,1),(8,2),(34,0);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+------------+-------+
| WeekNumber | Count |
+------------+-------+
|          5 |     1 |
|          8 |     2 |
|         34 |     0 |
+------------+-------+
3 rows in set (0.00 sec)

mysql>

Here is the query executed

mysql> select a.WeekNumber,ifnull(b.Count,0) Count
    -> from (select * from
    -> (select 1 WeekNumber union select 2 union select 3 union select 4 union select 5
    -> union select  6 union select  7 union select  8 union select  9 union select 10
    -> union select 11 union select 12 union select 13 union select 14 union select 15
    -> union select 16 union select 17 union select 18 union select 19 union select 20
    -> union select 21 union select 22 union select 23 union select 24 union select 25
    -> union select 26 union select 27 union select 28 union select 29 union select 30
    -> union select 31 union select 32 union select 33 union select 34 union select 35
    -> union select 36 union select 37 union select 38 union select 39 union select 40
    -> union select 41 union select 42 union select 43 union select 44 union select 45
    -> union select 46 union select 47 union select 48 union select 49 union select 50
    -> union select 51 union select 52 union select 53) aa) a
    -> left join mytable b using (WeekNumber)
    -> where WeekNumber between 2 and 35;
+------------+-------+
| WeekNumber | Count |
+------------+-------+
|          2 |     0 |
|          3 |     0 |
|          4 |     0 |
|          5 |     1 |
|          6 |     0 |
|          7 |     0 |
|          8 |     2 |
|          9 |     0 |
|         10 |     0 |
|         11 |     0 |
|         12 |     0 |
|         13 |     0 |
|         14 |     0 |
|         15 |     0 |
|         16 |     0 |
|         17 |     0 |
|         18 |     0 |
|         19 |     0 |
|         20 |     0 |
|         21 |     0 |
|         22 |     0 |
|         23 |     0 |
|         24 |     0 |
|         25 |     0 |
|         26 |     0 |
|         27 |     0 |
|         28 |     0 |
|         29 |     0 |
|         30 |     0 |
|         31 |     0 |
|         32 |     0 |
|         33 |     0 |
|         34 |     0 |
|         35 |     0 |
+------------+-------+
34 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

To do this easily in MySQL which does not have the functionality to generate "standalone" sequential series, you can first create a table with just the numbers 1-53 to JOIN with, then use the query;

SELECT num WeekNumber, IFNULL(count,0) Count
FROM MyTable 
RIGHT JOIN weeksequence 
  ON WeekNumber=num
WHERE num BETWEEN 2 AND 35
ORDER BY num;

Demo here.

Upvotes: 1

jerrymouse
jerrymouse

Reputation: 17812

WeekNumber is an aggregate field or a column? Your question may trivial solution like:

SELECT week, count FROM mytable where week between 2 and 35

Upvotes: 0

Related Questions