SQL to add a summary row to MySQL result set

问题:

If I have a MySQL table such as:

I want to use SQL to calculate the sum of the PositiveResult column and also the NegativeResult column. Normally I could simply do SUM(PositiveResult) in a query.

But what if I wanted to go a step further and place the totals in a row at the bottom of the result set:

Can this be achieved at the data level or is it a presentation layer issue? If it can be done by SQL, how might I do this? I am a bit of an SQL newbie.

Thanks to the respondents. I will now check things with the customer.

Also, can a text column be added so that the value of the last row of data is not shown in the summary row? Like this:

回答1:

I would also do this in the presentation layer, but you can do it MySQL...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,pos DECIMAL(5,2)
,neg DECIMAL(5,2)
);

INSERT INTO my_table VALUES
(1,0,0),
(2,1,-2.5),
(3,1.6,-1),
(4,1,-2);


SELECT COALESCE(id,'total') my_id,SUM(pos),SUM(neg) FROM my_table GROUP BY id WITH ROLLUP;

+-------+----------+----------+
| my_id | SUM(pos) | SUM(neg) |
+-------+----------+----------+
|     1 |     0.00 |     0.00 |
|     2 |     1.00 |    -2.50 |
|     3 |     1.60 |    -1.00 |
|     4 |     1.00 |    -2.00 |
|  total|     3.60 |    -5.50 |
+-------+----------+----------+
5 rows in set (0.02 sec)

Here's a hack for the amended problem - it ain't pretty but I think it works...

SELECT COALESCE(id,'') my_id
     , SUM(pos)
     , SUM(neg)
     , COALESCE(string,'') n
  FROM my_table 
 GROUP 
    BY id
     , string
  WITH ROLLUP
HAVING n <> '' OR my_id = ''
;

回答2:

select keyword,sum(positiveResults)+sum(NegativeResults)
from mytable
group by
Keyword

if you need the absolute value put sum(abs(NegativeResults)

回答3:

This should be handled at least one layer above the SQL query layer.

The initial query can fetch the detail info and then the application layer can calculate the aggregation (summary row). Or, a second db call to fetch the summary directly can be used (although this would be efficient only for cases where the calculation of the summary is very resource-intensive and a second db call is really necessary - most of the time the app layer can do it more efficiently).

The ordering/layout of the results (i.e. the detail rows followed by the "footer" summary row) should be handled at the presentation layer.

回答4:

I'd recommend doing this at the presentation layer. To do something like this in SQL is also possible.

create table test (
  keywordid int, 
  positiveresult decimal(10,2), 
  negativeresult decimal(10,2)
);
insert into test values 
(1, 0, 0), (2, 1, -2.5), (3, 1.6, -1), (4, 1, -2);

select * from (
    select keywordid, positiveresult, negativeresult
    from test
      union all
    select null, sum(positiveresult), sum(negativeresult) from test
) main
order by
case when keywordid is null then 1000000 else keywordid end;

I added ordering using a arbitrarily high number if keywordid is null to make sure the ordered recordset can be pulled easily by the view for displaying.

Result:
+-----------+----------------+----------------+
| keywordid | positiveresult | negativeresult |
+-----------+----------------+----------------+
|         1 |           0.00 |           0.00 |
|         2 |           1.00 |          -2.50 |
|         3 |           1.60 |          -1.00 |
|         4 |           1.00 |          -2.00 |
|      NULL |           3.60 |          -5.50 |
+-----------+----------------+----------------+