MySQL queries

[crusher88]

Say I have a table something like this:

[FONT=courier new]

|value|Type|Owner|
|1|A|Joe|
|1|B|Jim|
|2|A|Jim|
|1|A|Joe|
|3|C|Jim|

|_2|B|Joe|

[/FONT]

I want to run a mysql query and list the results like this Group by owner:

[FONT=courier new]---------------------------------------
|Owner|sum(A)|sum(😎|sum(C)|sum(Total)|
|Joe|2|2|0|4|
|Jim|2|1|3|6____|
---------------------------------------[/FONT]

My main problem is that I don't know how to execute multiple sum() functions in one query

Anyone?

[tekky]

I dont see that happening in a single SQL query... (I read that you want SUM(only if type A) sum(only if type 😎 etc etc...)

Using php its easy...

//query and such obviously goes here...
while($row = msyql_fetch_assoc($result))
{
    $user = $row['Owner'];
    $type = $row['Type'];
    $value = $row['value'];
    $mydata[$user][$type] += $value
}

this would result in...

$mydata['Joe']['a'] = 2
$mydata['Joe']['b'] = 2
$mydata['Jim']['a'] = 2
$mydata['Jim']['b'] = 1
$mydata['Jim']['c'] = 3

Hope that helps 😉

[crusher88]

Ok, thanks!

But how should the query/queries look like. I can't SUM() the same column more than once in one query with different WHERE clause like this:

$query = "SELECT *"
        . ", SUM(value) FROM table WHERE 1 AND type = 'A' GROUP BY owner"
        . ", SUM(value) FROM table WHERE 1 AND type = 'B' GROUP BY owner"
        . ", SUM(value) FROM table WHERE 1 AND type = 'C' GROUP BY owner";

$resultat  = mysql_query($query) or die("Error: " . mysql_error());

Should this work?

[Drakla]

Using tekky's excellent solution you'd just select the whole row each time, it's that simple - so a

SELECT * FROM tablename;

is the only query you'd need before the code

[crusher88]

Thanks!

I now got all the data in an array, but how the heck do I get the data into a nice display-friendly table like this. I'm missing the Group By Owner part:

[FONT=courier new]

|Owner|sum(A)|sum(😎|sum(C)|sum(Total)|
|Joe|2|2|0|4|
|Jim|2|1|3|6____|
[/FONT]

[tekky]

Originally posted by Drakla
Using tekky's excellent solution you'd just select the whole row each time, it's that simple - so a

SELECT * FROM tablename;

is the only query you'd need before the code

Correct, I coulda swore I put that in the original post.... 😕

maybe being 5am... I'm remember forgetting to think to put that in there... I think :rolleyes: (yah that was intentional... bored... another half hour before I can go home...) :glare:

[tekky]

I dont usually write hand out code... you should really attempt to write it on your own.... but i have to stay up for another hour to go sign crap at work soo....

echo '<table border=1';
foreach($mydata as $owner => $value)
{
    //breaks mydata into arrays of $owner[type]
    foreach($owner as $type => $val)
    {
            echo "<tr><td>$owner</td><td>$type</td><td>$val</td></tr>";
    }
}
echo '</table>';

I think that would do it...