CODEWITHSUNDEEP
sqloracle-database
espresso_coffee
espresso_coffee

Reputation: 6110

SQL error in Oracle?

I'm trying to get Total number of my count column in SQL Oracle but I keep getting THE error:

ORA-00907 missing the right parenthesis

Can anyone help me to fix this error?

Here is my query:

select isnull(s.years, 'Total') as Grade, count(*) as cnt1, c.firstname, c.lastname
from(
    Select s.years, SUM(count(*) as cnt1), c.firstname, c.lastname
    from employees cc 
    inner join building1 c on cc.id = c.bldid
    inner join members s on s.id = cc.id  
    where sec.LastName = 'Cook'
    group by s.years WITH ROLLUP, c.firstname, c.lastname
    order by s.years, c.firstname, c.lastname
    ) as cnt

Upvotes: 0

Views: 45

Answers (4)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select isnull(cnt.years, 'Total') Grade, count(*) as cnt1, cnt.firstname, cnt.lastname
from(
Select s.years, SUM(count(*)) as cnt1, c.firstname, c.lastname
from employees cc 
inner join building1 c on cc.id = c.bldid
inner join members s on s.id = cc.id  
where sec.LastName = 'Cook'
group by s.years WITH ROLLUP, c.firstname, c.lastname
order by s.years, c.firstname, c.lastname
) cnt  --you have to use this to reference columns in the outer query

You are selecting from an inner query named cnt. You should use it to reference columns in the outer query.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

In ORACLE you can not use ISNULL use COALESCE and remove alias name inside SUM

SELECT coalesce(s.years, 'Total') AS Grade, count(*) AS cnt1, c.firstname, c.lastname
FROM (SELECT s.years, SUM(count(*)) AS cnt1, c.firstname, c.lastname
    FROM employees cc 
    INNER JOIN building1 c on cc.id = c.bldid
    INNER JOIN members s on s.id = cc.id  
    WHEREsec.LastName = 'Cook'
    group by s.years WITH ROLLUP, c.firstname, c.lastname
    order by s.years, c.firstname, c.lastname
    ) as cnt

Upvotes: 2

Zsuzsa
Zsuzsa

Reputation: 427

c. has to be replaced with cnt.

Upvotes: 1

Matt
Matt

Reputation: 15061

Remove your alias from inside the SUM.

SELECT isnull(s.years, 'Total') AS Grade, count(*) AS cnt1, c.firstname, c.lastname
FROM (SELECT s.years, SUM(count(*)) AS cnt1, c.firstname, c.lastname
      FROM employees cc 
      INNER JOIN building1 c ON cc.id = c.bldid
      INNER JOIN members s ON s.id = cc.id  
      WHERE sec.LastName = 'Cook'
      GROUP BY s.years WITH ROLLUP, c.firstname, c.lastname
      ORDER BY s.years, c.firstname, c.lastname) AS cnt

Upvotes: 0

Related Questions