Hi,
Jay Blanchard wrote:
>Howdy,
>
>I need to write some data out to Excel spreadsheets for some of our managers
>to muddle with for projections. The query works fine...
>
>SELECT RecordID, RecordDate, count(*) AS Quantity
>FROM tblFOO
>Group By RecordID, RecordDate
>
>It returns;
>+----------+------------+----------+
>| RecordID | RecordDate | Quantity |
>+----------+------------+----------+
>| 100101 | 2002-03-21 | 6675 |
>| 100101 | 2002-03-22 | 6794 |
>| 100101 | 2002-03-23 | 2916 |
>| 100101 | 2002-03-24 | 1215 |
>| 100101 | 2002-03-25 | 6152 |
>| 100101 | 2002-03-26 | 5398 |
>+----------+------------+----------+
>
>as expected (Only a subset here, there are hundreds of rows). I want to
>place this data like this in an Excel table (bad ascii art example to
>follow);
>
>+------------+--------+--------+--------+
>| RecordID | 100101 | 100120 | 100131 |
>+------------+--------+--------+--------+
>| Date | | | |
>+------------+--------+--------+--------+
>| 2002-03-01 | | 12 | 130101 |
>+------------+--------+--------+--------+
>| 2002-03-21 | 6675 | | 1113 |
>+------------+--------+--------+--------+
>| 2002-03-22 | 6794 | 287 | 29045 |
>+------------+--------+--------+--------+
>| 2002-03-23 | 2916 | 6 | 233427 |
>+------------+--------+--------+--------+
>
>RecordID along the top row, dates down the side, quantities at the
>intersection that they belong. I could get an OBDC connection from Excel to
>MySQL and then write a complicated macro to do this, or if posssible (which
>is what I am really looking for) I would like to SELECT..INTO..OUTFILE an
>Excel file with the proper formatting.
>
>Any suggestions?
>
>Thanks!
>
>Jay Blanchard
>
here is a common way to do this staying within SQL. But it works only if
yuo know possible RecordID values in adnavce. i.e. for "general case"
you will need some scripting/programming to construct queres on the fly:
I will rewrite your table a little to give you a simplest example:
CREATE TABLE `foo` (
`r_id` enum('1','2','3','4','5') default NULL,
`d` date default NULL
)
Insert some 'foo' info
mysql> select * from foo order by r_id, d;
+------+------------+
| r_id | d |
+------+------------+
| 1 | 2002-04-30 |
| 1 | 2002-05-01 |
| 1 | 2002-05-02 |
| 2 | 2002-04-30 |
| 2 | 2002-04-30 |
| 2 | 2002-04-30 |
| 2 | 2002-05-01 |
| 2 | 2002-05-02 |
| 3 | 2002-04-30 |
| 4 | 2002-04-30 |
| 4 | 2002-05-01 |
| 5 | 2002-05-01 |
| 5 | 2002-05-01 |
+------+------------+
Here is same query as yours but with little addition :):
mysql> select d, r_id, count(*) as q_count, sum(1) as q_sum from foo
group by d, r_id;
+------------+------+---------+-------+
| d | r_id | q_count | q_sum |
+------------+------+---------+-------+
| 2002-04-30 | 1 | 1 | 1 |
| 2002-04-30 | 2 | 3 | 3 |
| 2002-04-30 | 3 | 1 | 1 |
| 2002-04-30 | 4 | 1 | 1 |
| 2002-05-01 | 1 | 1 | 1 |
| 2002-05-01 | 2 | 1 | 1 |
| 2002-05-01 | 4 | 1 | 1 |
| 2002-05-01 | 5 | 2 | 2 |
| 2002-05-02 | 1 | 1 | 1 |
| 2002-05-02 | 2 | 1 | 1 |
+------------+------+---------+-------+
Note that columns q_count and q_sum contain same value. Do you see why
both are correct?
Let's use that fact and add if() in sum()
mysql> select d, r_id, sum(1) as q_total, if(r_id = 1, 1, 0) as q_id1
from foo group by d, r_id;
+------------+------+---------+-------+
| d | r_id | q_total | q_id1 |
+------------+------+---------+-------+
| 2002-04-30 | 1 | 1 | 1 |
| 2002-04-30 | 2 | 3 | 0 |
| 2002-04-30 | 3 | 1 | 0 |
| 2002-04-30 | 4 | 1 | 0 |
| 2002-05-01 | 1 | 1 | 1 |
| 2002-05-01 | 2 | 1 | 0 |
| 2002-05-01 | 4 | 1 | 0 |
| 2002-05-01 | 5 | 2 | 0 |
| 2002-05-02 | 1 | 1 | 1 |
| 2002-05-02 | 2 | 1 | 0 |
+------------+------+---------+-------+
This is whole magic :)
We know in advance there are 5 different ids so add some if's ...
mysql> select d, r_id, sum(if(r_id = 1, 1, 0)) as q_id1, sum(if(r_id =
2, 1, 0)) as q_id2, sum(if(r_id = 3, 1, 0)) as q_id3, sum(if(r_id = 4,
1, 0)) as q_id4, sum(if(r_id = 5, 1, 0)) as q_id5, count(*) as q_total
from foo group by d, r_id;
+------------+------+-------+-------+-------+-------+-------+---------+
| d | r_id | q_id1 | q_id2 | q_id3 | q_id4 | q_id5 | q_total |
+------------+------+-------+-------+-------+-------+-------+---------+
| 2002-04-30 | 1 | 1 | 0 | 0 | 0 | 0 | 1 |
| 2002-04-30 | 2 | 0 | 3 | 0 | 0 | 0 | 3 |
| 2002-04-30 | 3 | 0 | 0 | 1 | 0 | 0 | 1 |
| 2002-04-30 | 4 | 0 | 0 | 0 | 1 | 0 | 1 |
| 2002-05-01 | 1 | 1 | 0 | 0 | 0 | 0 | 1 |
| 2002-05-01 | 2 | 0 | 1 | 0 | 0 | 0 | 1 |
| 2002-05-01 | 4 | 0 | 0 | 0 | 1 | 0 | 1 |
| 2002-05-01 | 5 | 0 | 0 | 0 | 0 | 2 | 2 |
| 2002-05-02 | 1 | 1 | 0 | 0 | 0 | 0 | 1 |
| 2002-05-02 | 2 | 0 | 1 | 0 | 0 | 0 | 1 |
+------------+------+-------+-------+-------+-------+-------+---------+
10 rows in set (0.00 sec)
Well w have redundant rows now - r_id is presented both as a column and
a row - let's get rid of it remove from group by.
I left it just to see result is correct
Here is your final 'Magic query'
mysql> select d, sum(if(r_id = 1, 1, 0)) as q_id1, sum(if(r_id = 2, 1,
0)) as q_id2, sum(if(r_id = 3, 1, 0)) as q_id3, sum(if(r_id = 4, 1, 0))
as q_id4, sum(if(r_id = 5, 1, 0)) as q_id5, count(*) as q_total from
foo group by d;
+------------+-------+-------+-------+-------+-------+---------+
| d | q_id1 | q_id2 | q_id3 | q_id4 | q_id5 | q_total |
+------------+-------+-------+-------+-------+-------+---------+
| 2002-04-30 | 1 | 3 | 1 | 1 | 0 | 6 |
| 2002-05-01 | 1 | 1 | 0 | 1 | 2 | 5 |
| 2002-05-02 | 1 | 1 | 0 | 0 | 0 | 2 |
+------------+-------+-------+-------+-------+-------+---------+
3 rows in set (0.00 sec)
Are you satisfied? :)
Detailed analisys of this technique can be found in a great (MUST read)
article at:
http://www.mysql.com/articles/wizard/index.html
http://www.devshed.com/Server_Side/MySQL/MySQLWiz/page1.html
--
Best regards
--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
