CODEWITHSUNDEEP
sqloracle-database
drali
drali

Reputation: 25

How to Find the sum for count(*) column which is came from another union result query

I am working in some query that should give the sum for count(*) column ,

my query :

SELECT sap_sys_id,
  stock_id,
  form,
  COUNT(*)
FROM tbl_doc
WHERE stock_id=
  (SELECT Q1_stock_id-1 FROM tbl_id_virtualpool
  )
AND spool_name NOT LIKE '%MXC%'
GROUP BY sap_sys_id,
  stock_id,
  form
UNION ALL
SELECT sap_sys_id,
  stock_id,
  form,
  COUNT(*)
FROM tbl_document_log
WHERE stock_id=
  (SELECT Q2_stock_id-1 FROM tbl_id_virtualpool
  )
AND spool_name NOT LIKE '%MXC%'
GROUP BY sap_sys_id,
  stock_id,
  form
ORDER BY form;

the result what i have :

SAP 111111  ZEBFSF_FICO1_sys     6865
SAP 222222  ZEBFSF_FICO2_sys     836
SAP 222222  ZEBFSF_FICO3_sys     83646
SAP 333333  ZEBFSF_FICO4_sys     326
SAP 222222  ZEBFSF_FICO5_sys     19

while the result what is desired is :

SAP 111111  ZEBFSF_FICO1_sys     6865
SAP 222222  ZEBFSF_FICO2_sys     836
SAP 222222  ZEBFSF_FICO3_sys     83646
SAP 333333  ZEBFSF_FICO4_sys     326
SAP 222222  ZEBFSF_FICO5_sys     19
                Total          91 692

any ideas how this can be done, Thanks in advance

Upvotes: 0

Views: 31

Answers (1)

AvielNiego
AvielNiego

Reputation: 1233

Using Analytic function maybe you could add a new column with the desired sum

SELECT sap_sys_id,stock_id,form, COUNT, sum(count) OVER (  ) as sum 
from (
SELECT sap_sys_id,
       stock_id,
       form,
       COUNT(*) as count
FROM tbl_doc
WHERE stock_id =
      (SELECT Q1_stock_id - 1
       FROM tbl_id_virtualpool
      )
  AND spool_name NOT LIKE '%MXC%'
GROUP BY sap_sys_id,
         stock_id,
         form
UNION ALL
SELECT sap_sys_id,
       stock_id,
       form,
       COUNT(*) as count
FROM tbl_document_log
WHERE stock_id =
      (SELECT Q2_stock_id - 1
       FROM tbl_id_virtualpool
      )
  AND spool_name NOT LIKE '%MXC%'
GROUP BY sap_sys_id,
         stock_id,
         form
ORDER BY form);

Upvotes: 2

Related Questions