Clever Geek Handbook

How SUM from MySQL for every n record

I have the following result from a query:

+---------------+------+------+------+------+------+------+------+-------+
| order_main_id | S36  | S37  | S38  | S39  | S40  | S41  | S42  | total |
+---------------+------+------+------+------+------+------+------+-------+
|            26 |  127 |  247 |  335 |  333 |  223 |  111 |   18 |  1394 | 
|            26 |  323 |  606 |  772 |  765 |  573 |  312 |  154 |  3505 | 
|            38 |   25 |   35 |   35 |   35 |   20 | NULL | NULL |   150 | 
|            38 |   25 |   35 |   35 |   35 |   20 | NULL | NULL |   150 | 
|            39 |   65 |   86 |   86 |   42 |   21 | NULL | NULL |   300 | 
|            39 |   42 |   58 |   58 |   28 |   14 | NULL | NULL |   200 | 
|            35 |   11 |   20 |   21 |   18 |    9 |    2 | NULL |    81 | 
|            35 |   10 |   25 |   30 |   23 |   12 |    1 | NULL |   101 | 
+---------------+------+------+------+------+------+------+------+-------+

I would like to insert SUM before entering different ones order_main_id

, it would be like this result:

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

How to do it?

+2


source to share


3 answers


You will need to write a second query that uses the GROUP BY order_main_id.

Something like:



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

TO

+4


source


You can do it in a single query, but with union all

(indeed two queries, but the result sets are combined to make one wonderful 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 what order by

affects completeness union all

, not just the last query. So your result will 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 is not, it is a detailed line or a summary line, and order_main_id

for it. You can always (and probably should) hide this column in your presentation layer.

+2


source


For things like this, I think you should use a report library (like Crystal Reports), it will save you tons of trouble, check out JasperReports and similar osalt projects

0


source






More articles:


All Articles