I've a table like this:
---------------------------------------
| S No | item | Col1 | Col2 |
| ------------------------------------|
| 1 | carrot | 111 | 2 |
| 2 | broccoli | 222 | 3 |
| 3 | spinach | 333 | 2 |
| 4 | swiss | 444 | 3 |
| 5 | cheddar | 5555 | 5 |
| 6 | cheddar | 6666 | 6 |
| 7 | onion | 7777 | 3 |
There are many columns like Col1, Col2, Coln with all integer values. I want to take sum of all columns and get the name of top 5 columns with their names and sum values as response of query. I can do this with top 5 records but find it hard to do with columns. I'm doing it for rows like this:
$q1 = mysqli_query($con, "SELECT `File Name`, `Kudos` FROM `{$_SESSION['username']}` ORDER BY `Kudos` DESC LIMIT 5");
while($row = mysqli_fetch_array($q1)) {
$myCols[] = $row;
}
print_r($myCols); // Works fine
print_r gives me:
Array
(
[0] => Array
(
[0] => AAAA
[File Name] => AAAA
[1] => 343
[Kudos] => 343
)
[1] => Array
(
[0] => Test File Again
[File Name] => Test File Again
[1] => 45
[Kudos] => 45
)
[2] => Array
(
[0] => TEST FILE
[File Name] => TEST FILE
[1] => 3
[Kudos] => 3
)
)
Looks good. How do I same for column with maximum sum values for whole column? Anyone out there? Thanks!
Just add them up and use them in the order by
:
SELECT `File Name`, (col1 + col2 + col3 . . . ) as totalsum
FROM `{$_SESSION['username']}
ORDER BY totalsum DESC
LIMIT 5;