CODEWITHSUNDEEP
sqloracle-database
tickle
tickle

Reputation: 3

Need to list the most popular car

hi this is what i have so far

SELECT person.PID,
  person.FIRSTNAME,
  person.SUBURB,
  INSURED_ITEM.MODEL
FROM person,
  client,
  policy,
  INSURED_ITEM,
  INSURED_BY
WHERE person.PID    = client.PID
AND client.CID      = insured_by.cid
AND INSURED_BY.PNO  = policy.PNO
AND insured_item.id = policy.id
AND person.SUBURB   = 'Randwick'
ORDER BY COUNT(*) DESC ;

what im trying to do is list the most popular car in Randwick.

is this corrected?

Upvotes: 0

Views: 72

Answers (2)

Vinnie
Vinnie

Reputation: 3929

You need to use a group by in order to use the aggregate count(*)

SELECT person.PID,
  person.FIRSTNAME,
  person.SUBURB,
  INSURED_ITEM.MODEL,
  COUNT(*) AS PopularityIndex
FROM person
  JOIN client ON person.PID    = client.PID
  JOIN INSURED_BY ON client.CID      = insured_by.cid
  JOIN policy ON INSURED_BY.PNO  = policy.PNO
  JOIN INSURED_ITEM ON insured_item.id = policy.id
WHERE person.SUBURB   = 'Randwick'
GROUP BY person.PID,
  person.FIRSTNAME,
  person.SUBURB,
  INSURED_ITEM.MODEL
ORDER BY PopularityIndex DESC ;

Per your comment, to return the top 3 in Oracle, you can add that to the select statement

SELECT *
FROM ( SELECT person.PID,
  person.FIRSTNAME,
  person.SUBURB,
  INSURED_ITEM.MODEL,
  COUNT(*) AS PopularityIndex
FROM person
  JOIN client ON person.PID    = client.PID
  JOIN INSURED_BY ON client.CID      = insured_by.cid
  JOIN policy ON INSURED_BY.PNO  = policy.PNO
  JOIN INSURED_ITEM ON insured_item.id = policy.id
WHERE person.SUBURB   = 'Randwick'
GROUP BY person.PID,
  person.FIRSTNAME,
  person.SUBURB,
  INSURED_ITEM.MODEL
ORDER BY PopularityIndex DESC )
WHERE ROWNUM >= 3;

You could also add a having clause that will allow you to set a threshold on the PopularityIndex

SELECT person.PID,
  person.FIRSTNAME,
  person.SUBURB,
  INSURED_ITEM.MODEL,
  COUNT(*) AS PopularityIndex
FROM person
  JOIN client ON person.PID    = client.PID
  JOIN INSURED_BY ON client.CID      = insured_by.cid
  JOIN policy ON INSURED_BY.PNO  = policy.PNO
  JOIN INSURED_ITEM ON insured_item.id = policy.id
WHERE person.SUBURB   = 'Randwick'
GROUP BY person.PID,
  person.FIRSTNAME,
  person.SUBURB,
  INSURED_ITEM.MODEL
HAVING COUNT(*) > 3
ORDER BY PopularityIndex DESC ;

Upvotes: 1

tickle
tickle

Reputation: 3

what you gave me helped but i changed it to suit,

SELECT 
INSURED_ITEM.MODEL,
COUNT(*) as POPULAR
FROM person
JOIN client ON person.PID    = client.PID
JOIN INSURED_BY ON client.CID      = insured_by.cid
JOIN policy ON INSURED_BY.PNO  = policy.PNO
JOIN INSURED_ITEM ON insured_item.id = policy.id
WHERE person.SUBURB   = 'Randwick'
GROUP BY 
INSURED_ITEM.MODEL
having count(*) > = 2
order by count(*) desc;

Upvotes: 0

Related Questions