Error while retreving values from DB

Question

I need to get title, count and percentage from table REPORT_VIEW_PAGE. That works fine.

SELECT DISTINCT TITLE, COUNT(TITLE) AS tot, ROUND(COUNT(TITLE)/(
SELECT COUNT(*) FROM REPORT_VIEW_PAGE), 4) AS per
FROM REPORT_VIEW_PAGE 
WHERE DAYCOUNT > '0'
GROUP BY TITLE
ORDER BY TITLE ASC

But if tried to retrieve ID and TYPE like

SELECT DISTINCT TITLE, ID, TYPE, COUNT(TITLE) AS tot, ROUND(COUNT(TITLE)/(
SELECT COUNT(*) FROM REPORT_VIEW_PAGE), 4) AS per
FROM REPORT_VIEW_PAGE 
WHERE DAYCOUNT > '0'
GROUP BY TITLE
ORDER BY TITLE ASC

i'm getting the error:

ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause:
*Action: Error at Line: 12 Column: 24

Can anyone help.

Please edit your question with sample data and desired results. Clearly, if you add `id` and `type` to the `group by`, you will fix the problem. But that is probably not the query you want. — Gordon Linoff, Jul 28 '15 at 12:52
In addition to what Gordon said, if you're doing a `group by` then the `distinct` is unnecessary, confusing and needs to be removed. — Boneist, Jul 28 '15 at 13:04

davegreen100 · Accepted Answer · 2015-07-28T13:10:57.320

1

you need to group by the additional columns

SELECT TITLE, ID, TYPE, COUNT(TITLE) AS tot, ROUND(COUNT(TITLE)/(
SELECT COUNT(*) FROM REPORT_VIEW_PAGE), 4) AS per
FROM REPORT_VIEW_PAGE 
WHERE DAYCOUNT > '0'
GROUP BY TITLE, Id, TYPE
ORDER BY TITLE ASC

edited Jul 28 '15 at 13:10

answered Jul 28 '15 at 12:56

davegreen100

2,055
3
13
24

could yo mark as the correct answer please – davegreen100 Jul 28 '15 at 13:30

Error while retreving values from DB

1 Answers1