0
SELECT COUNT(*) AS customer, t.state
  FROM tbl_parcel_item t
 WHERE t.courier_name='Tnt'
   AND t.date = '2018-03-12'
   AND t.ship_status NOT IN (0,1,9,10)
 GROUP BY t.state`

Based on the sql above the results would be: enter image description here How to make it get total of the result above for example : enter image description here

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Mokan Velan
  • 161
  • 1
  • 1
  • 7

4 Answers4

0

Use with rollup:

SELECT COUNT(*) AS customer, t.state`
FROM tbl_parcel_item t
WHERE t.courier_name = 'Tnt' AND
      t.date = '2018-03-12' AND
      t.ship_status NOT IN (0, 1, 9, 10)
GROUP BY t.state WITH ROLLUP;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Use union all:

`SELECT COUNT(*) AS customer, t.state`
`FROM tbl_parcel_item t`
`WHERE t.courier_name='Tnt'`
`AND t.date = '2018-03-12'`
`AND t.ship_status NOT IN (0,1,9,10)`
`GROUP BY t.state`
`UNION ALL`
`SELECT COUNT(*) AS customer, 'Total' as state`
`FROM tbl_parcel_item t`
`WHERE t.courier_name='Tnt'`
`AND t.date = '2018-03-12'`
`AND t.ship_status NOT IN (0,1,9,10)`
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0

Use ROLLUP:

SELECT
    COALESCE(t.state, 'TOTAL') AS state,
    COUNT(*) AS customer
FROM tbl_parcel_item t
WHERE
    t.courier_name = 'Tnt' AND
    t.date = '2018-03-12'  AND
    t.ship_status NOT IN (0,1,9,10)
GROUP BY t.state WITH ROLLUP;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

01) WITH ROLLUP as Explained in the other answers

02) Use Union Query

  SELECT  t.state, COUNT(*) AS customer FROM tbl_parcel_item t WHERE t.courier_name='Tnt' AND t.date = '2018-03-12' AND t.ship_status NOT IN (0,1,9,10) GROUP BY t.state
  UNION 
  SELECT  'Total', COUNT(*) AS customer FROM tbl_parcel_item t WHERE t.courier_name='Tnt' AND t.date = '2018-03-12' AND t.ship_status NOT IN (0,1,9,10)
Ravi Kanex
  • 1
  • 1