runtimetask COUNT / SUM / AVG / MIN / MAX · easy
Average price by category
For merchandise products that have a category, return category_id and avg_price: the average list price, rounded to 2 decimals. Products with no list price are ignored by the average.
what to return
- Columns
category_idavg_pricein this order- Rows
- 15 rows · any order
- Skill
- Average a measure per group. Refresher: Group into summaries →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result15 rows · any order
| category_id | avg_price |
|---|---|
| 1 | 2.01 |
| 2 | 2.86 |
| 3 | 4.41 |
first 3 of 15 rows
data you can query
Retail dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
products6 columns · 5,304 rows
| stock_codetext · unique id | descriptiontext | category_idnumber · links to categories | list_pricedecimal | first_sold_atdate & time | is_merchandisenumber |
|---|---|---|---|---|---|
| 10002 | INFLATABLE POLITICAL GLOBE | 17 | 0.85 | 2009-12-01 09:08:00 | 1 |
| 10002R | ROBOT PENCIL SHARPNER | 8 | 5.17 | 2009-12-02 14:43:00 | 1 |
| 10080 | GROOVY CACTUS INFLATABLE | 17 | 0.39 | 2009-12-02 16:02:00 | 1 |
| 10109 | BENDY COLOUR PENCILS | 8 | 0.42 | 2009-12-03 12:31:00 | 1 |
| 10120 | DOGGY RUBBER | 17 | 0.21 | 2009-12-01 16:17:00 | 1 |
| 10123C | HEARTS WRAPPING TAPE | 8 | 0.65 | 2009-12-01 14:28:00 | 1 |
- stock_codetext · unique id
- 10002
- descriptiontext
- INFLATABLE POLITICAL GLOBE
- category_idnumber · links to categories
- 17
- list_pricedecimal
- 0.85
- first_sold_atdate & time
- 2009-12-01 09:08:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10002R
- descriptiontext
- ROBOT PENCIL SHARPNER
- category_idnumber · links to categories
- 8
- list_pricedecimal
- 5.17
- first_sold_atdate & time
- 2009-12-02 14:43:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10080
- descriptiontext
- GROOVY CACTUS INFLATABLE
- category_idnumber · links to categories
- 17
- list_pricedecimal
- 0.39
- first_sold_atdate & time
- 2009-12-02 16:02:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10109
- descriptiontext
- BENDY COLOUR PENCILS
- category_idnumber · links to categories
- 8
- list_pricedecimal
- 0.42
- first_sold_atdate & time
- 2009-12-03 12:31:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10120
- descriptiontext
- DOGGY RUBBER
- category_idnumber · links to categories
- 17
- list_pricedecimal
- 0.21
- first_sold_atdate & time
- 2009-12-01 16:17:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10123C
- descriptiontext
- HEARTS WRAPPING TAPE
- category_idnumber · links to categories
- 8
- list_pricedecimal
- 0.65
- first_sold_atdate & time
- 2009-12-01 14:28:00
- is_merchandisenumber
- 1
first 6 of 5,304 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.