Oracle 12 SQL – ORA-00933, ORA-0923, ORA-00979 What am I doing wrong?

I am currently facing a bit of a wall, I am attempting to take information from two different tables, have them displayed side by side with the information recording by count. One being the total amount in the first table with a specific value, the second is the value of different column.

Table A.current is the same as B.id

However the information I want is total of hits in table A with current and display the information of B.name instead of b.id

I’ve attempted many things, so far I keep having an issue where it says about from is incorrect or group is incorrect:

select  
    count(pk.id) as "Total",
    lc.fullyqualifiedname as "Name"
from
    tsu pk,
    location lc
where   
    pk.locationid = lc.id
group by 
    lc.id
having 
    lc.id  = :ID;

From the code above I get the error 00923

If anyone could help me – where did I go wrong?

What I need to end up with would be

Column 1 – total of hits ( count(*) ) which have location as lc.id Column 2 – the name which the lc.id is represented as fullyqualifiedname being displayed. providing the value of a different column in the sable table.

EDIT :

select  count(pk.id) as "Total",
    lc.fullyqualifiedname as "Name"
from    tsu pk,
    location lc
where   pk.locationid = lc.id
group by lc.id
having lc.id  = :ID;

This script works however it displays column 1 from table lc, I would like to have column name from lc display.

— Issue is resolved with answered script.

Answer

For single lc.id there is no need to use GROUP BY:

select count(pk.id) as "Total",
       min(lc.fullyqualifiedname) as "Name"   -- here agg function
from tsu pk
join location lc                              -- join syntax is preferred
  on pk.locationid = lc.id
where lc.id  = :ID;                           -- filtering with where

Alternatively:

select count(pk.id) as "Total",
       min(lc.fullyqualifiedname) as "Name",
       lc.id
from tsu pk
join location lc
  on pk.locationid = lc.id
---where lc.id IN (...)
group by lc.id;

Advertisement

Answer