CODEWITHSUNDEEP
phpmysqlsql
Ahmed
Ahmed

Reputation: 43

how to select and sum in single sql query

Is it possible or another solution to sum and select multiple rows in single sql query and print with while looping like that:

$query = mysql_query("SELECT SUM(Total), * FROM table");
while ($fetch = mysql_fetch_row($query)) {
    echo $fetch[a];
    echo $fetch[b];
    echo $fetch[c];
}

Upvotes: 1

Views: 7836

Answers (7)

Theepak
Theepak

Reputation: 21

We can use CROSS APPLY for calculation.

SELECT SumTotal, A.* 
FROM table A 
CROSS APPLY (SELECT SUM(Total) SumTotal FROM Table) B

Upvotes: 1

Fixus
Fixus

Reputation: 4641

isn't it simpler and faster to sum inside the loop instead using complex query ?

you could just make simple (faster) select * from table and in your while loop make the sum while looping. it will be faster than making query with subquery

Upvotes: 0

Barmar
Barmar

Reputation: 780673

Use a JOIN with a subquery that calculates the total.

SELECT SumTotal, a.*
FROM Table a
JOIN (SELECT SUM(Total) SumTotal
      FROM Table) b

Upvotes: 4

MaveRick
MaveRick

Reputation: 1191

you can do it as what @491243 suggested

SELECT (SELECT SUM(Total) FROM `table`) AS totalSum, * FROM `table`

But this is not recommended because this will cause that SQL Engine calculate sum of the column total on each row it's selecting from the database and sending a new column with the results to php with identical values in the column totalSum fields ,

better go for 2 queries. one for selecting the rows and the other to get the total

Upvotes: 2

bjackfly
bjackfly

Reputation: 3336

Usually you want to group by a column so that you can sum by a group and then it will give you separate rows for each category_id in this example

SELECT category_id, SUM(price) as totalprice
FROM products 
GROUP BY category_id

Upvotes: 0

John Woo
John Woo

Reputation: 263683

Do you mean this?

SELECT (SELECT SUM(Total) FROM `table`) totalSum, a.* FROM `table` a

Upvotes: 2

Shankar Narayana Damodaran
Shankar Narayana Damodaran

Reputation: 68446

Make use of GROUP BY clause.

$query = mysql_query("SELECT *,SUM(Total) as TotalSUM FROM table GROUP BY Total");

    while ($fetch = mysql_fetch_row($query)) {

    echo $fetch[a];
    echo $fetch[b];
    echo $fetch[c];


    }

Upvotes: 0

Related Questions