CODEWITHSUNDEEP
mysqlsql
Amirul Fahmi
Amirul Fahmi

Reputation: 299

MYSQL Get total from group by

How to get total(sum) from each group from this query? Is it possible to select the value from 'Total'? *I'd try with rollup but gives me error : "Incorrect usage of CUBE/ROLLUP and ORDER BY" . Mysql version 5.0

My Query :

SELECT datatest.region, datatest.cusname, datatest.mt, COUNT(dt.region) AS rank
FROM datatest
LEFT JOIN datatest AS dt ON datatest.region = dt.region AND datatest.mt < dt.mt
GROUP BY datatest.region, datatest.mt
HAVING COUNT(dt.mt) < 2
ORDER BY datatest.region, datatest.mt DESC

Data Schema :

CREATE TABLE datatest (region varchar(50) ,cusname varchar(50) ,mt int(50) ) ;

insert  into datatest values 
('central', 'J.O.E SUPPLIER SDN BHD', 135),
('central', 'AURAJUBLI SDN BHD', 12),
('central', 'NEW MT CENTURY SDN. BHD.', 1242),
('central', 'KMT SUPPLY SDN BHD', 42),
('eastern', 'SASHWIN SDN. BHD.', 5),
('eastern', 'INFARMS SDN . BHD', 765),
('eastern', 'GMAZ TRADING', 105),
('eastern', 'TMI PACIFIC SDN. BHD.', 1025),
('southern', 'KMT SUPPLY SDN BHD', 80135),
('southern', 'GMAZ TRADING', 85),
('southern', 'NEW MT CENTURY SDN. BHD.', 128),
('southern', 'INFARMS SDN . BHD', 67);

Desired Result :

result

Upvotes: 4

Views: 203

Answers (5)

Nick
Nick

Reputation: 147166

Because you have no SUM in your query, you can't use WITH ROLLUP. But you can wrap the query into a subquery and then SUM(mt) outside that and use GROUP BY ... WITH ROLLUP:

SELECT region, cusname, SUM(mt) AS mt, rank FROM (
SELECT datatest.region, datatest.cusname, datatest.mt, COUNT(dt.region) AS rank
FROM datatest
LEFT JOIN datatest AS dt ON datatest.region = dt.region AND datatest.mt < dt.mt
GROUP BY datatest.region, datatest.mt
HAVING COUNT(dt.region) < 2
ORDER BY datatest.region, datatest.mt DESC) d
GROUP BY region, cusname WITH ROLLUP

Output:

region      cusname                     mt  rank    
central     J.O.E SUPPLIER SDN BHD      135     1
central     NEW MT CENTURY SDN. BHD.    1242    0
central     Total                       1377    0
eastern     INFARMS SDN . BHD           765     1
eastern     TMI PACIFIC SDN. BHD.       1025    0
eastern     Total                       1790    0
southern    KMT SUPPLY SDN BHD          80135   0
southern    NEW MT CENTURY SDN. BHD.    128     1
southern    Total                       80263   1
Grand Total                             83430   1

Update

To keep the regions sorted by rank, it's necessary to make this query a subquery and then order the results from it:

SELECT *
FROM (SELECT COALESCE(region, 'Grand Total') AS region, IF(region IS NULL, '', COALESCE(cusname, 'Total')) AS cusname, SUM(mt) AS mt, rank 
      FROM (SELECT datatest.region, datatest.cusname, datatest.mt, COUNT(dt.region) AS rank
            FROM datatest
            LEFT JOIN datatest AS dt ON datatest.region = dt.region AND datatest.mt < dt.mt
            GROUP BY datatest.region, datatest.mt
            HAVING COUNT(dt.region) < 2
            ORDER BY datatest.region, datatest.mt DESC) d
      GROUP BY region, cusname WITH ROLLUP) r
ORDER BY region='Grand Total', region, cusname='Total', rank

Output:

region      cusname                     mt  rank    
central     NEW MT CENTURY SDN. BHD.    1242    0
central     J.O.E SUPPLIER SDN BHD      135     1
central     Total                       1377    0
eastern     TMI PACIFIC SDN. BHD.       1025    0
eastern     INFARMS SDN . BHD           765     1
eastern     Total                       1790    0
southern    KMT SUPPLY SDN BHD          80135   0
southern    NEW MT CENTURY SDN. BHD.    128     1
southern    Total                       80263   1
Grand Total                             83430   1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

This is tough to do without analytic functions, but since we now have MySQL 8+ we can use them:

WITH cte AS (
    SELECT region, cusname, mt,
        RANK() OVER (PARTITION BY region ORDER BY mt DESC) - 1 rnk
    FROM datatest
)

SELECT region, COALESCE(cusname, 'total') AS cusname, SUM(mt) AS total
FROM cte
WHERE rnk < 2
GROUP BY region, cusname WITH ROLLUP;

enter image description here

Demo

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Something like this should work - although I think it's a bit 'hacky' to omit unaggregated columns from the GROUP BY...

SELECT a.region
     , a.cusname
     , SUM(mt) mt
     , rolling
     , rank
  FROM 
     ( SELECT x.region
          , x.cusname
          , x.mt
          , SUM(y.mt) rolling
          , COUNT(*) rank 
       FROM datatest x 
       JOIN datatest y 
         ON y.region = x.region 
        AND y.mt >= x.mt 
      GROUP 
         BY x.region
          , x.cusname
          , x.mt 
     HAVING COUNT(*) <=2 
     ) a
 GROUP 
    BY a.region
     , a.rank
  WITH ROLLUP;

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

You can try to write a subquery to get the rank number in select clause, then use UNION ALLcombine total by group and group results.

Schema (MySQL v5.7)

CREATE TABLE datatest (region varchar(50) ,cusname varchar(50) ,mt int(50) ) ;

insert  into datatest values ('central', 'J.O.E SUPPLIER SDN BHD', 135);
insert  into datatest values ('central', 'AURAJUBLI SDN BHD', 12); 
insert  into datatest values ('central', 'NEW MT CENTURY SDN. BHD.', 1242); 
insert  into datatest values ('central', 'KMT SUPPLY SDN BHD', 42); 
insert  into datatest values ('eastern', 'SASHWIN SDN. BHD.', 5);
insert  into datatest values ('eastern', 'INFARMS SDN . BHD', 765); 
insert  into datatest values ('eastern', 'GMAZ TRADING', 105);
insert  into datatest values ('eastern', 'TMI PACIFIC SDN. BHD.', 1025); 
insert  into datatest values ('southern', 'KMT SUPPLY SDN BHD', 80135); 
insert  into datatest values ('southern', 'GMAZ TRADING', 85); 
insert  into datatest values ('southern', 'NEW MT CENTURY SDN. BHD.', 128); 
insert  into datatest values ('southern', 'INFARMS SDN . BHD', 67);

Query #1

SELECT CASE WHEN rank IS NULL THEN '' ELSE region end regions,
       cusname,
       mt,
       rank
FROM 
(
SELECT region, cusname, mt,rank
FROM (
    SELECT *,(select count(*) 
        from datatest tt
         where tt.region = t1.region and tt.mt > t1.mt) rank
    FROM datatest t1
) t1
where t1.rank <= 1
UNION ALL
SELECT region, 'totle', SUM(mt),null
FROM (
    SELECT *,(select count(*) 
        from datatest tt
         where tt.region = t1.region and tt.mt > t1.mt) rank
    FROM datatest t1
) t1
where t1.rank <= 1
GROUP BY region
) t1
ORDER BY region,rank desc;

| regions  | cusname                  | mt    | rank |
| -------- | ------------------------ | ----- | ---- |
| central  | J.O.E SUPPLIER SDN BHD   | 135   | 1    |
| central  | NEW MT CENTURY SDN. BHD. | 1242  | 0    |
|          | totle                    | 1377  |      |
| eastern  | INFARMS SDN . BHD        | 765   | 1    |
| eastern  | TMI PACIFIC SDN. BHD.    | 1025  | 0    |
|          | totle                    | 1790  |      |
| southern | NEW MT CENTURY SDN. BHD. | 128   | 1    |
| southern | KMT SUPPLY SDN BHD       | 80135 | 0    |
|          | totle                    | 80263 |      |

View on DB Fiddle

Upvotes: 0

theboringdeveloper
theboringdeveloper

Reputation: 1485

What u can do is:

Use another query to calculate sum and then Union them.

SELECT datatest.region, datatest.cusname, datatest.mt, COUNT(dt.region) AS rank
FROM datatest
LEFT JOIN datatest AS dt ON datatest.region = dt.region AND datatest.mt < dt.mt
GROUP BY datatest.region, datatest.mt
HAVING COUNT(dt.mt) < 2
ORDER BY datatest.region, datatest.mt DESC
UNION
SELECT null, 'Total', SUM(mt), null
FROM datatest

Upvotes: 0

Related Questions