CODEWITHSUNDEEP
sqloracle-databasegroup-by
zpnq
zpnq

Reputation: 1

Select from two different tables while using GROUP BY

I am using Oracle SQL Dev The problem is with using the GROUP BY

I have two tables one called CAR that holds details about cars and another called OWNERS - people have been an owner of a CAR.

CAR

plateNo    make

246810     Ford
121416     BMW
182022     VW
162398     Tesla

.

OWNER

name      plateNo 

Joe       246810
Greg      246810
Brad      246810
Sam       121416
Kim       121416
Sal       182022
Betty     162398

Im trying to list how many owners each car make has had and display make and number of owners

I can get the count for plateNo from OWNER

SELECT count(plateNo) AS NO_OWNERS
FROM OWNER
GROUP BY plateNo
ORDER BY COUNT(plateNo);

The result would be

NO_OWNERS
3
2
1
1

When i try to add 'make' to the query like this

SELECT count(OWNER.plateNo), CAR.MAKE 
FROM OWNER, CAR
WHERE OWNER.plateNo = CAR.plateNo
GROUP BY OWNER.plateNo
ORDER BY COUNT(OWNER.plateNo);

I get the error "not a GROUP BY expression" the result I am looking for would be like this

NO_OWNERS   make
3           Ford
2           BMW
1           VW
1           Tesla

How do I go about this type of query?

Further still how can i also display the plateNo in the result?

Upvotes: 0

Views: 61

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

SELECT c.make, c.plateno, count(*)
FROM OWNER o
      INNER JOIN CAR c ON o.plateNo = c.plateNo
GROUP BY c.make, c.plateno
ORDER BY COUNT(*) DESC;

Another simple rule: repeat all non-aggregating columns of the select clause in the group by clause

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Then use the correct field in the GROUP BY. Along the way, fix your JOIN syntax as well:

SELECT count(*), c.MAKE 
FROM OWNER o JOIN
     CAR c
     ON o.plateNo = c.plateNo
GROUP BY c.MAKE
ORDER BY COUNT(*) DESC;

Simple rule: Never use commas in the FROM clause; always use explicit JOIN syntax.

Upvotes: 1

Related Questions