I am having a harder time than I would expect writing a query that includes aggregate functions, queries multiple tables, and results in accurate numbers and hoping I can get some help.
Category table example
The category is what I need to report on:
|----|-----------|
| id | name |
|----|-----------|
| 1 | furniture |
| 2 | music |
| 3 | kitchen |
| 4 | adventure |
|----|-----------|
Product table example
Product table example:
|-----|----------------|-------------|
| id | name | category_id |
|-----|----------------|-------------|
| 101 | couch | 1 |
| 102 | chair | 1 |
| 103 | drum | 2 |
| 104 | flute | 2 |
| 105 | pot | 3 |
| 106 | pan | 3 |
| 107 | kitchen sink | 3 |
| 108 | unicorn saddle | 4 |
| 109 | unicorn shoes | 4 |
| 110 | horse shampoo | 4 |
|-----|----------------|-------------|
Activity table example
The views data we want to sum (by category) found in the activity table:
|----|------------|-------|
| id | product_id | views |
|----|------------|-------|
| 1 | 101 | 1000 |
| 2 | 102 | 2000 |
| 3 | 103 | 3000 |
| 4 | 104 | 4000 |
| 5 | 105 | 5000 |
| 6 | 106 | 6000 |
| 7 | 107 | 7000 |
| 8 | 108 | 8000 |
| 9 | 109 | 9000 |
| 10 | 110 | 10000 |
|----|------------|-------|
Sales table example
The sales table that we want to put query the average sale (again by category). Note that the vendor_id is important as a single product can be carried by multiple vendors. I am leaving out the vendor table as its not needed for this question (we can just query using the vendor ID in later examples).
|----|------------|-----------|--------|
| id | product_id | vendor_id | amount |
|----|------------|-----------|--------|
| 1 | 101 | 1 | 1000 |
| 2 | 102 | 1 | 900 |
| 3 | 103 | 1 | 2000 |
| 4 | 105 | 1 | 3000 |
| 5 | 107 | 1 | 5000 |
| 6 | 101 | 2 | 600 |
| 7 | 103 | 2 | 7000 |
| 8 | 105 | 2 | 8000 |
| 9 | 107 | 2 | 1000 |
| 10 | 108 | 1 | 500 |
| 11 | 109 | 1 | 600 |
| 12 | 108 | 2 | 400 |
| 13 | 109 | 2 | 500 |
|----|------------|-----------|--------|
Desired Output
Below is the desired output:
**Note that some vendors do not carry some products, therefore, meaning that there is no average sales amount..or, in other words, there are no records in the sales table for some products found in the product table (e.g. no vendors carry horse shampoo). For this reason, I want to make sure that any averages or sums that I am using are in fact accurate. To be specific here, if a **.
|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|
| category | count_products | sum_views | average_sales | sum_views_where_sales_=>_1000 | sum_views_sales_<_1000 |
|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|
| adventure | 3 | 27000 | 500 | 0 | 27000 |
| furniture | 2 | 3000 | 833 | 0 | 3000 |
| kitchen | 3 | 18000 | 3000 | 6000 | 12000 |
| music | 2 | 7000 | 5000 | 7000 | 0 |
|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|
Current state of query
First to get an accurate count on products and views:
SELECT cat.name AS category,
count(distinct p.name) AS product,
sum(a.views) AS views
FROM
category AS cat,
product AS p,
activity AS a
WHERE
cat.id=p.category_id
AND
p.id=a.product_id
GROUP BY
category;
sidenote: I would love to not have to use the distinct in the above query. Any ideas here would be great.
Accurate result displaying views by category:
|-----------|---------|-------|
| category | product | views |
|-----------|---------|-------|
| Adventure | 3 | 27000 |
| Furniture | 2 | 3000 |
| Kitchen | 3 | 18000 |
| Music | 2 | 7000 |
|-----------|---------|-------|
Everything looks good until I start joining to the other tables:
SELECT cat.name AS category,
count(distinct p.name) AS product,
sum(a.views) AS views,
round(avg(s.amount)) AS sales_amount
FROM
category AS cat,
product AS p,
activity AS a,
sales AS s
WHERE
cat.id=p.category_id
AND
p.id=a.product_id
AND
p.id=s.product_id
AND
s.vendor_id=1
GROUP BY
category;
PROBLEM OUTPUT
|-----------|---------|-------|------------------|
| category | product | views | avg_sales_amount |
|-----------|---------|-------|------------------|
| Adventure | 2 | 17000 | 550 |
| Furniture | 2 | 3000 | 950 |
| Kitchen | 2 | 12000 | 4000 |
| Music | 1 | 3000 | 2000 |
|-----------|---------|-------|------------------|
As you can notice I am getting further from the desired output when I start querying by vendor_id to get the average sales amount. To be specific, the product column no longer results in the correct amount of products because not all vendors carry all the same products making the s.vendor_id=1 filter difficult. I have to use it to be able to filter these reports by vendor while still getting accurate sums on the view field.
I have tried the above queries using a LEFT JOIN but still ends up with innacurrate results and not sure what needs to happen, possibly a sub query of some sort?