CODEWITHSUNDEEP
sqloracle-database
drup
drup

Reputation: 1157

Get sum of column in sql

My query is

SELECT 
    AIP.TERMID,
    (SELECT COUNT(RAU.TERMNAME) 
     FROM REPORT_API_USAGE RAU 
     WHERE RAU.TERMID = AIP.TERMID AND RAU.VOCID = 4) AS page_views
FROM REPORT_API_PAGES AIP
GROUP BY AIP.TERMID

I need to get the total of column page_views. How can I get it? Please help. PHP and ORACLE.

enter image description here

I need total of page_views as 2 (1+1). I need to get it as new row without changing the existing query.

SELECT 
    AIP.TERMID,
    (SELECT COUNT(RAU.TERMNAME) 
     FROM REPORT_API_USAGE RAU 
     WHERE RAU.TERMID = AIP.TERMID AND RAU.VOCID = 4) AS page_views,
    (SELECT SUM(COUNT(RAU.TERMNAME)) 
     FROM REPORT_API_USAGE RAU 
     WHERE RAU.VOCID = 4 GROUP BY AIP.TERMID) AS page_views
FROM REPORT_API_PAGES AIP
GROUP BY AIP.TERMID`

I tried this but giving a result 4 instead of 2

enter image description here

Upvotes: 0

Views: 108

Answers (2)

A Hocevar
A Hocevar

Reputation: 726

What exactly do you want to show ? The total of page views per TERMID ?

SELECT aip.TERMID,
  COUNT(rau.TERMNAME) AS PAGE_VIEWS
FROM REPORT_API_PAGES aip
  JOIN REPORT_API_USAGE rau ON rau.TERMID = aip.TERMID
WHERE rau.VOCID = 4
GROUP BY aip.TERMID

Although, with your current query, the join is not even needed (unless you have rows in REPORT_API_USAGE with no parent in REPORT_API_PAGES)

SELECT rau.TERMID,
  COUNT(rau.TERMNAME) AS PAGE_VIEWS
FROM REPORT_API_USAGE rau
WHERE rau.VOCID = 4
GROUP BY rau.TERMID

EDIT: So, based on the update of your question, if you only want the total of pageviews, you can use

SELECT COUNT(rau.TERMNAME) AS page_views
FROM REPORT_API_PAGES aip
  JOIN REPORT_API_USAGE rau ON rau.TERMID = aip.TERMID
WHERE rau.VOCID = 4

(The join is not necessary, it allows you to only count the page views for pages where the TERMID is defined in the REPORT_API_PAGES table)

EDIT2: Based on your second update, I suggest you to use SUM(PAGE_VIEWS) OVER () on your subquery

SELECT TERMID,
  PAGE_VIEWS,
SUM(PAGE_VIEWS) OVER () AS TOTAL_PAGE_VIEWS
FROM (
  SELECT aip.TERMID,
    COUNT(rau.TERMNAME) AS PAGE_VIEWS
  FROM REPORT_API_PAGES aip
    LEFT OUTER JOIN REPORT_API_USAGE rau ON (rau.TERMID = aip.TERMID AND rau.VOCID = 4)
  GROUP BY aip.TERMID)

Obviously you can replace the subquery with your original query as well.

Upvotes: 1

smali
smali

Reputation: 4805

SELECT AIP.TERMID,
(
  select count(RAU.TERMNAME) 
  FROM 
      REPORT_API_USAGE RAU 
  WHERE
      RAU.TERMID = AIP.TERMID AND RAU.VOCID = 4
) as page_views
from 
  REPORT_API_PAGES AIP
GROUP BY 
  AIP.TERMID

just try modifying this to like this

select sum(page_views)
(
  SELECT AIP.TERMID,
  (
   select count(RAU.TERMNAME) 
   FROM 
      REPORT_API_USAGE RAU 
   WHERE
      RAU.TERMID = AIP.TERMID AND RAU.VOCID = 4
  ) as page_views
  from 
    REPORT_API_PAGES AIP
  GROUP BY 
    AIP.TERMID
) from dual;

Upvotes: 1

Related Questions