ansaurus

Question

How to SUM from MySQL for every n record

Answer 1

+4 A:

You'll need to write a second Query which makes use of GROUP BY order_main_id.

Something like:

SELECT sum(S41+...) FROM yourTable GROUP BY orderMainId

K

KB22 2009-08-28 10:21:00

Yes, i did a second query like your suggest, that's why a got those value. But i would like to do this by one query. Any thought ?

Stephen 2009-08-28 10:28:04

No, in my understanding this is not possible with one query. You could achive this by writing a stored procedure which fills a temporary table with the results of your two queries in order to get your desired output.

KB22 2009-08-28 10:36:32

Thanks KB22,This is a light for me, that this is not possible.

Stephen 2009-08-28 10:51:47

This is definitely possible and not something you need to loop through or do a temp table for. In fact, that's very, very bad advice to solve this problem. Just use a `union all`, and life will be peachy!

Eric 2009-08-28 11:27:24

Ok, I was'nt aware of union all. my d'oh

KB22 2009-08-28 12:32:57

KB22, you are still the best.

Stephen 2009-08-29 03:03:53

Answer 2

+2 A:

You can actually do this in one query, but with a union all (really two queries, but the result sets are combined to make one awesome result set):

select
    order_main_id,
    S36,
    S37,
    S38,
    S39,
    S40,
    S41,
    S42,
    S36 + S37 + S38 + S39 + S40 + S41 + S42 as total,
    'Detail' as rowtype
from
    tblA
union all
select
    order_main_id,
    sum(S36),
    sum(S37),
    sum(S38),
    sum(S39),
    sum(S40),
    sum(S41),
    sum(S42),
    sum(S36 + S37 + S38 + S39 + S40 + S41 + S42),
    'Summary' as rowtype
from
    tblA
group by
    order_main_id
order by
    order_main_id, RowType

Remember that the order by affects the entirety of the union all, not just the last query. So, your resultset would look like this:

+---------------+------+------+------+------+------+------+------+-------+---------+
| order_main_id | S36  | S37  | S38  | S39  | S40  | S41  | S42  | total | rowtype |
+---------------+------+------+------+------+------+------+------+-------+---------+
|            26 |  127 |  247 |  335 |  333 |  223 |  111 |   18 |  1394 | Detail  |
|            26 |  323 |  606 |  772 |  765 |  573 |  312 |  154 |  3505 | Detail  |
|            26 |  450 |  853 | 1107 | 1098 |  796 |  423 |  172 |  4899 | Summary |
|            35 |   11 |   20 |   21 |   18 |    9 |    2 | NULL |    81 | Detail  |
|            35 |   10 |   25 |   30 |   23 |   12 |    1 | NULL |   101 | Detail  |
|            35 |   21 |   45 |   51 |   41 |   21 |    3 | NULL |   182 | Summary |
|            38 |   25 |   35 |   35 |   35 |   20 | NULL | NULL |   150 | Detail  |
|            38 |   25 |   35 |   35 |   35 |   20 | NULL | NULL |   150 | Detail  |
|            38 |   50 |   70 |   70 |   70 |   40 | NULL | NULL |   300 | Summary |
|            39 |   65 |   86 |   86 |   42 |   21 | NULL | NULL |   300 | Detail  |
|            39 |   42 |   58 |   58 |   28 |   14 | NULL | NULL |   200 | Detail  |
|            39 |  107 |  144 |  144 |   70 |   35 | NULL | NULL |   500 | Summary |
+---------------+------+------+------+------+------+------+------+-------+---------+

This way, you know what is and what isn't a detail or summary row, and the order_main_id that it's for. You could always (and probably should) hide this column in your presentation layer.

Eric 2009-08-28 11:22:01

+1 Right, or let your reporting tool handle this

Andomar 2009-08-28 12:16:50

That really works. As S36 until S42 are aliases of another SUM before UNION ALL, those aliases not recoqnized after UNION ALL, so i need to make aliasing again, The rowType is genius addition. Many thanks Eric.

Stephen 2009-08-29 03:00:17

Answer 3

A:

For things like these I think you should use a reporting library(such as Crystal Reports), it'll save you a lot of trouble, check JasperReports and similar projects on osalt

Diaa Sami 2009-08-28 11:36:19

ansaurus

tags:

views:

answers:

How to SUM from MySQL for every n record

related questions