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.
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;