Multiple table join query, with count in Oracle SQL

Question

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

user359040 · Accepted Answer · 2013-04-28 14:21:25Z

2

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

answered Apr 28, 2013 at 14:21

user359040

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple table join query, with count in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related