tricky timetable based query
Sam Russo
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
I recieve a delimited file whose fields are:
day,slot,subject,room
An example of this file is:
2,1,Mat,R1
3,1,Sci,R6
1,2,Sci,R6
3,2,Mat,R3
1,3,Eng,R2
2,3,Eng,R5
1,4,Mat,R7
3,4,Eng,R9
I need a mysql query that will generate a timetable which looks like:
Day1 Day2 Day3
-------------------------------
1 Mat R1 Sci R6
2 Sci R6 Mat R3
3 Eng R2 Eng R5
4 Mat R7 Eng R9
Any help would be appreciated
sam
**********************************************************************
This message is intended for the addressee named and may contain
privileged information or confidential information or both. If you
are not the intended recipient please delete it and notify the sender.
**********************************************************************
--Alt-Boundary-28286.23037343--
SGr...@unimin.com
What you are trying to do is an example of what is called "pivoting" a
table or "creating a pivot table".
SELECT slot
, max(if(day=1, concat(subject,' ',room), '')) as day1
, max(if(day=2, concat(subject,' ',room), '')) as day2
, max(if(day=3, concat(subject,' ',room), '')) as day3
, max(if(day=4, concat(subject,' ',room), '')) as day4
, max(if(day=5, concat(subject,' ',room), '')) as day5
from schedule
group by slot
The max() decides between an entry and a blank ( the entry will win if one
exists) while the group by lines everything up on the same row. Friendly
caution: If more than one entry exists for the same day and time, you will
only see the one that is alphabetically "greater".
if you wanted to see how many classes are scheduled by day for each slot
(to check for conflicts) you could modify the query:
SELECT slot
, sum(if(day=1,1,0)) as day1
, sum(if(day=2,1,0)) as day2
, sum(if(day=3,1,0)) as day3
, sum(if(day=4,1,0)) as day4
, sum(if(day=5,1,0)) as day5
from schedule
group by slot
There is definitely a pattern. The columns you want as "row headers" are
listed both in the SELECT **and** in the GROUP BY clauses. The values you
want as columns are selectively chosen ( by the IF() functions) to return
something or nothing so that one of the aggregate functions (MIN, MAX, SUM,
AVG, etc) can condense those rows and columns into single values.
The decisions for what gets counted in any column can be as complex as you
like. If you don't like the IF() function you can use CASE statements or
anything else to help you provide the correct set of values for the
aggregate function you are using on that column. You can mix and match
aggregate functions to be what you need. Let's say you join the appropriate
tables together to form a data set that looks like :
day, slot, subject, student, grade
and you wanted to build a chart showing the statistics of grades vs. days
and subject. You could use this type of query to work that out:
SELECT day, subject
, AVG(grade) as average
, MIN(grade) as lowest
, MAX(grade) as highest
from <necessary tables>
group by day, subject
Now while I didn't actually need to choose values for my columns you can
see that if I wanted to "pivot" that table by days (each column
representing the statistics for just one day) I would have to modify they
query to read:
SELECT subject
, AVG(IF(day=1, grade,null)) as D1_average
, MIN(IF(day=1, grade, null)) as D1_lowest
, MAX(IF(day=1,grade,null)) as D1_highest
, AVG(IF(day=2, grade,null)) as D2_average
, MIN(IF(day=2, grade, null)) as D2_lowest
, MAX(IF(day=2,grade,null)) as D2_highest
, .... (repeat for rest of the days)
from <necessary tables>
group by day, subject
Notice how I had to change the IF function to return NULL values? That kept
the AVG() from counting all of the other grades for the same subject but
from different days. If I had used a constant, like zero, then those
zeroes would have counted against us in the aggregate calculation. (The
same goes for the MIN and MAX functions)
I hope this helps!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Sam Russo"
<s...@rwb.com.au> To: my...@lists.mysql.com
cc:
06/18/2004 08:34 Fax to:
AM Subject: tricky timetable based query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw
Harald Fuchs
"Sam Russo" <s...@rwb.com.au> writes:
> I recieve a delimited file whose fields are:
> day,slot,subject,room
> An example of this file is:
> 2,1,Mat,R1
> 3,1,Sci,R6
> 1,2,Sci,R6
> 3,2,Mat,R3
> 1,3,Eng,R2
> 2,3,Eng,R5
> 1,4,Mat,R7
> 3,4,Eng,R9
> I need a mysql query that will generate a timetable which looks like:
> Day1 Day2 Day3
> -------------------------------
> 1 Mat R1 Sci R6
> 2 Sci R6 Mat R3
> 3 Eng R2 Eng R5
> 4 Mat R7 Eng R9
If there are only three days, you could use the following:
CREATE TEMPORARY TABLE slots (slot TINYINT UNSIGNED NOT NULL PRIMARY KEY) AS
SELECT DISTINCT slot FROM tbl;
SELECT s.slot,
coalesce(concat(t1.subject, ' ', t1.room), '') AS Day1,
coalesce(concat(t2.subject, ' ', t2.room), '') AS Day2,
coalesce(concat(t3.subject, ' ', t3.room), '') AS Day3
FROM slots s
LEFT JOIN tbl t1 ON t1.slot = s.slot AND t1.day = 1
LEFT JOIN tbl t2 ON t2.slot = s.slot AND t2.day = 2
LEFT JOIN tbl t3 ON t3.slot = s.slot AND t3.day = 3;
Michael Stassen
Harald Fuchs wrote:
We could do without the temporary table by simply moving the DISTINCT to the
SELECT, though I expect that's less efficient. We should also probably
explicitly ORDER BY slot. In this case, an index on slot (as you did in the
temporary table) would probably help.
SELECT DISTINCT s.slot,
coalesce(concat(t1.subject, ' ', t1.room), '') AS Day1,
coalesce(concat(t2.subject, ' ', t2.room), '') AS Day2,
coalesce(concat(t3.subject, ' ', t3.room), '') AS Day3
FROM slots s
LEFT JOIN tbl t1 ON t1.slot = s.slot AND t1.day = 1
LEFT JOIN tbl t2 ON t2.slot = s.slot AND t2.day = 2
LEFT JOIN tbl t3 ON t3.slot = s.slot AND t3.day = 3
ORDER BY s.slot;
We could also accomplish both the DISTINCT and the ORDER BY using GROUP BY
instead:
SELECT s.slot,
coalesce(concat(t1.subject, ' ', t1.room), '') AS Day1,
coalesce(concat(t2.subject, ' ', t2.room), '') AS Day2,
coalesce(concat(t3.subject, ' ', t3.room), '') AS Day3
FROM slots s
LEFT JOIN tbl t1 ON t1.slot = s.slot AND t1.day = 1
LEFT JOIN tbl t2 ON t2.slot = s.slot AND t2.day = 2
LEFT JOIN tbl t3 ON t3.slot = s.slot AND t3.day = 3
GROUP BY s.slot;
Michael