| Re: Transforming object properties? [message #178148] |
Tue, 15 May 2012 00:28  |
Jeff North
Messages: 58
Registered: November 2010
Karma:
|
Member |
|
|
On Wed, 9 May 2012 14:57:10 -0700 (PDT), in comp.lang.php Mike
<mpearl1(at)gmail(dot)com>
<21352106.71.1336600630360.JavaMail.geo-discussion-forums@pbmf10>
wrote:
> | I didn't do a good job of presenting this, my apologies.
> |
> | I have two tables, one with user info, the other with commission amounts. I need to get a list of sales people that gives the total commissions for each status (there are four of them). My SQL query looks like this:
> |
> | SELECT name, status, SUM(commission)
> | FROM transactions
> | GROUP BY name, status
> | ORDER BY name ASC
> |
> | This is giving the right results, but I will have multiple rows for each name, showing the status and total for that status:
> |
> | Joe new 10.00
> | Joe pending 44.00
> | Joe closed 88.00
> | etc.
> |
> | I need a single row for each name, something like this:
> |
> | name new pending closed
> | Joe 10.00 44.00 88.00
> |
> | Is there a way to create a sql query that will return this? If not, how can I get the data in that format?
> |
> | Thanks again!
> | Joe
SELECT name
, SUM(if(status='new',commission,0) ) AS `new`
, SUM(if(status='pending',commission,0) ) AS `pending`
, SUM(if(status='closed',commission,0) ) AS `closed`
FROM transactions
GROUP BY name
ORDER BY name
Now to put this back on-topic for this group.
As you can see there is a lot of repetition here and what if the data
was dynamic where you didn't know the field names in advance. Or
someone decides they need to add new status types. Or you wanted to
add the count of each status type.
You could go and change all your code or you could make your query
dynamic from the outset.
Get the list of the table values:
$sql = "SELECT status FROM transaction GROUP BY status";
$records = // use whatever method to get the results from the database
$sql = "SELECT name "; //--- start building query, re-use $sql
foreach($records as $r) {
$sql .= ",SUM(if(status='{$r}',commission,0) ) AS `{$r}`";
$sql .= ",COUNT(if(status='{$r}',1,0) ) AS `{$r}Count`";
}
//--- now append the rest of the query
$sql .= "FROM transactions
GROUP BY name
ORDER BY name";
$xtab = // use whatever method to get the results from the database
Now your boss comes up to you and tells you that they want the
minimum, average and maximum values. You say, no problem - what order
do you want the results?
Your boss turns to you and says 'For being such a smart-arse you can
now do the totals for each quarter'.
If you want to know how to produce more complex cross tab queries then
head over to:
http://dev.mysql.com/tech-resources/articles/wizard/index.html
its a bit long winded but I find very useful.
HTH
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]