BorlandTalk.com

Colin B Maharaj · Guest

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.

Jayme Jeffman · Guest

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" <noreply (AT) myhost (DOT) com> escreveu na mensagem
news:45204f6a (AT) newsgroups (DOT) borland.com...