[EMAIL PROTECTED] wrote:
Hi,<snipped>
I have this query problem, I know its not related to MySQL server...
Got this data
Number cost type
1 10 NDD
2 200 LOCAL
1 20 NDD
2 15 NDD
2 30 LOCAL
1 5 LOCAL
2 10 NDD
2 15 LOCAL
how to create a query that the output looks like this
number cost_NDD cost_LOCAL
1 30 5
2 25 245
anybody can help?I really appreciate it.
Looks very familiar. My company does Telecommunications cost analysis...
Anyway, you can use a crosstab query, but I prefer using if() statements, because it forces the columns to appear even if there is no data for that column. Of course you have to know what data will be in the column to start with, but that is usually the case...
I would use:
select
Number,
cost_LOCAL=sum(if(type='LOCAL',cost,0)),
cost_NDD=sum(if(type='NDD',cost,0))
from MyTable
group by Number
As I said you can research the crosstab way of doing it, but if there is no (for example) IDD data in your table, then it will drop the cost_IDD field completely, and your app will whinge about it.
Hope this helps.
Also, if you need professional Telcommunications consulting / cost analysis, see our website :)
Dan
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
---------------------------------------------------------------------
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
