Board index » cppbuilder » MySQL syntax for an inverted table

2006-10-02 07:30:00 AM
cppbuilder23
I have this query (and result)...
select desttype A, sum(duration) B,
count(cost) C, sum(cost) D from calls
group by desttype;
+------+----------+--------+---------+
| A | B | C | D |
+------+----------+--------+---------+
| NULL | 20179324 | 122048 | 0.00 |
| AR | 13155339 | 70456 | 0.00 |
| CE | 1385003 | 16232 | 0.00 |
| IT | 86003 | 292 | 1814.60 |
| LD | 526292 | 2213 | 7438.29 |
| LO | 261093 | 557 | 0.00 |
| SD | 8684 | 41 | 643.50 |
+------+----------+--------+---------+
7 rows in set (0.56 sec)
But I want to turn it "90 degrees" i.e. make the tuples into columns and
the columns into tuples. So far this is what I have come up with,
partially......
select * from
(select sum(cost) AR from calls where desttype='AR') as tmp1,
(select sum(cost) LO from calls where desttype='LO') as tmp2,
(select sum(cost) LD from calls where desttype='LD') as tmp3,
(select sum(cost) IT from calls where desttype='IT') as tmp4;
+------+------+---------+---------+
| AR | LO | LD | IT |
+------+------+---------+---------+
| 0.00 | 0.00 | 7438.29 | 1814.60 |
+------+------+---------+---------+
1 row in set (1.05 sec)
But I think this is way too much processing and this is just the cost part.
Any advice would be nice. I am using MySQL 5x.
 

Hi,
I am afraid there is not another way of doing this. As far as I know
the SQL language is thought for retrieve column values from tables
as rows.
HTH
Jayme.
"Colin B Maharaj" < XXXX@XXXXX.COM >escreveu na mensagem