Mysql: How to join a query to find results from another table-CodePudding

I have two tables:

TABLE A

Unique_id	id	price
1	1	10.50
2	3	14.70
3	1	12.44

TABLE B

Unique_id	Date	Category	Store	Cost
1	2022/03/12	Shoes	A	13.24
2	2022/04/15	Hats	A	15.24
3	2021/11/03	Shoes	B	22.31
4	2000/12/14	Shoes	A	15.33

I need to filter TABLE A on a known id to get the Unique_id and average price to join to Table B.

Using this information I need to know which stores this item was sold in.

I then need to create a results table displaying the stores and the amount of days sales were recorded in the stores - regardless of whether the sales are associated with the id and the average cost.

To put it more simply I can break down the task into 2 separate commands:

SELECT AVG(price)
FROM table_a
WHERE id = 1
GROUP BY unique_id;

SELECT store, COUNT(date), AVG(cost) 
FROM table_b 
WHERE category = 'Shoes' 
GROUP BY store;

The unique_id should inform the join but when I join the tables it messes up my COUNT function and only counts the days in which the id is connected - not the total store sales days.

The results should look something like this:

Store	AVG price	COUNT days	AVG cost
A	10.50.	3	14.60.
B	12.44	1.	22.31.

CodePudding user response：

I wwas hard to grasp, what you wanted, but after some thinking and your clarification, it can be solved as the code shows

CREATE TABLE TableA
    (`Unique_id` int, `id` int, `price` DECIMAL(10,2))
;
    
INSERT INTO TableA
    (`Unique_id`, `id`, `price`)
VALUES
    (1, 1, 10.50),
    (2, 3, 14.70),
    (3, 1, 12.44)
;

CREATE TABLE TableB
    (`Unique_id` int, `Date` datetime, `Category` varchar(5), `Store` varchar(1), `Cost` DECIMAL(10,2))
;
    
INSERT INTO TableB
    (`Unique_id`, `Date`, `Category`, `Store`, `Cost`)
VALUES
    (1, '2022-03-12 01:00:00', 'Shoes', 'A', 13.24),
    (2, '2022-04-15 02:00:00', 'Hats', 'A', 15.24),
    (3, '2021-11-03 01:00:00', 'Shoes', 'B', 22.31),
    (4, '2000-12-14 01:00:00', 'Shoes', 'A', 15.33)

SELECT 
    B.`Store`
    , AVG(A.`price`) price
    , (SELECT COUNT(*) FROM TableB WHERE `Store` = B.`Store` ) count_
    , (SELECT AVG(
    `cost`) FROM TableB WHERE `Store` = B.`Store` ) price
FROM TableA A
JOIN TableB B ON A.`Unique_id` = B.`Unique_id`
WHERE B.`Category` = 'Shoes'
GROUP BY B.`Store`

Store |     price | count_ |     price
:---- | --------: | -----: | --------:
A     | 10.500000 |      3 | 14.603333
B     | 12.440000 |      1 | 22.310000

db<>fiddle here

CodePudding user response：

This should be the query you are after. Mainly you simply join the rows using an outer join, because not every table_b row has a match in table_a.

Then, the only hindrance is that you only want to consider shoes in your average price. For this to happen you use conditional aggregation (a CASE expression inside the aggregation function).

select
  b.store,
  avg(case when b.category = 'Shoes' then a.price end) as avg_shoe_price,
  count(b.unique_id) as count_b_rows,
  avg(b.cost) as avg_cost
from table_b b
left outer join table_a a on a.unique_id = b.unique_id
group by b.store
order by b.store;

I must admit, it took me ages to understand what you want and where these numbers result from. The main reason for this is that you have WHERE table_a.id = 1 in your query, but this must not be applied to get the result you are showing. Next time please look to it that your description, queries and sample data match.

(And then, I think that names like table_a, table_b and unique_id don't help understanding this. If table_a were called prices instead and table_b costs and unique_id were called cost_id then, I wouldn't have had to wonder how the tables are related (by id? by unique id?) and wouldn't have had to look again and again which table the cost resides in, which table has a price and which table is the outer joined one while looking at the problem, the requested result and while writing my query.)