CODEWITHSUNDEEP
sqloracle-databasejoin
Insert_into_knowledge
Insert_into_knowledge

Reputation: 23

Multiple table join query, with count in Oracle SQL

I'm trying to count the number of inspections of each property in YR_Inspection table by counting the number of times the property number occurs in the table, i then need to display this next to the town the city the property is located in and the branch the property belongs to all in the same query. Here is the link to my ERD to try and give this question some context,

enter image description here

http://www.gliffy.com/pubdoc/4239520/L.png

this is the code so far, it works so far but as soon as i add YR_Branch.CITY i get,

ORA-00979: not a GROUP BY expression

SELECT YR_Property.PROPERTYNUM, COUNT(YR_Inspection.PROPERTYNUM) AS Number_of_inspections
FROM YR_Property  
INNER JOIN YR_Inspection 
    ON YR_Property.PROPERTYNUM = YR_Inspection.PROPERTYNUM 
JOIN YR_Branch
    ON YR_Property.BRANCHNUM = YR_Branch.BRANCHNUM
GROUP BY YR_Property.PROPERTYNUM

Upvotes: 2

Views: 9377

Answers (1)

user359040
user359040

Reputation:

To add the branch number and the city for the branch, add max values for each to the query - like so:

SELECT YR_Property.PROPERTYNUM, 
       COUNT(YR_Inspection.PROPERTYNUM) AS Number_of_inspections,
       MAX(YR_Branch.BRANCHNUM) AS Branch_Number,
       MAX(YR_Branch.CITY) AS Branch_City
FROM YR_Property  
INNER JOIN YR_Inspection 
    ON YR_Property.PROPERTYNUM = YR_Inspection.PROPERTYNUM 
JOIN YR_Branch
    ON YR_Property.BRANCHNUM = YR_Branch.BRANCHNUM
GROUP BY YR_Property.PROPERTYNUM

Upvotes: 2

Related Questions