CODEWITHSUNDEEP
mysqlsql
user1641898
user1641898

Reputation:

Count occurrences in a table then use for finding average

table 1 

categoryid   categoryname   categorydescription
1               a             zzzzzzz
2               b              yyyyyy
3               c               uuuuu

table 2

carid caryear carmodel carprice catid(foreign key to category id )
1        xxxx    aaaa    xxxx    1
2       xxxx     bbbb    xxxx    3
3       xxxx     cccc    xxxx    4
3       xxxx     dddd    xxxx    3
4       xxxxx    eeee    xxxx    1

results

categoryname                               averageprice                         total cars
a               sum price of same category car / no of same category cars           1
b               sum price of same category car / no of same category cars           2
c               sum price of same category car / no of same category cars           2

Upvotes: 0

Views: 196

Answers (1)

ruakh
ruakh

Reputation: 183504

You can write:

 SELECT category.categoryname,
        AVG(car.carprice) AS "averageprice",
        COUNT(car.carid) AS "total cars"
   FROM category
   LEFT
  OUTER
   JOIN car
     ON car.catid = category.categoryid
  GROUP
     BY category.categoryname
;

Notes:

  • You didn't mention the names of your tables, so I had to guess.
  • This will include categories that don't have any cars. If you'd only like to include categories that have at least one car, drop the LEFT OUTER part.

Upvotes: 2

Related Questions