MySQL UNION combining data from same table-CodePudding

So I have a table with product sales.

Table:

id
name
price
amount
date

I am trying to grab the average price of the items per day. If I run both my queries seperarely they work, I've been trying to figure this out all day and I cant seem to figure it out. So here's my query.

    SELECT id, date, name, AVG(price) as today FROM sales 
        WHERE DATE(FROM_UNIXTIME(date)) = (CURRENT_DATE()) AND id = 1 GROUP BY id
    UNION 
    SELECT id, date, name, AVG(price) as yesterday FROM sales 
        WHERE DATE(FROM_UNIXTIme(date)) = (CURRENT_DATE() - INTERVAL 1 DAY) AND id = 1 GROUP BY id) a group by id

I need it to be displayed as ,

[{"id":1,"name":"test","today":200, "yesterday": 100}]

I can get it so it displays both but for some reason it would show [{"id":1,"date":1645881110,"name":"test","today":200},{"id":1,"date":1645881110,"name":"test","today":100}]

Which not sure why it displays "today" for both even tho its grabbing the correct data for the days.

If anyone can point me in the right direction that would be awesome and greatly appreciated.

CodePudding user response：

Your "today" string is a column name. The column names in a resultset generated by a UNION operation are set by the first query in the union. That's why that AVG(price) value from your second query gets the name "today".

It looks like you want today's and yesterday's average prices to show up on the same row of the result set. If that's what you want a UNION isn't the way to do it: their purpose is to add rows to the result set and you want to add a column.

Try something like this instead.

SELECT 
  id, date, name, 
  AVG(IF(DATE(FROM_UNIXTIME(date)) = (CURRENT_DATE(), price, NULL) ) as today,
  AVG(IF(DATE(FROM_UNIXTIME(date)) = (CURRENT_DATE() - INTERVAL 1 DAY, price, NULL) ) as yesterday
FROM sales 
WHERE DATE(FROM_UNIXTIME(date)) BETWEEN CURRENT_DATE() - INTERVAL 1 DAY
                                    AND CURRENT_DATE()
  AND id = 1
GROUP BY id;

The expression AVG(condition, value, NULL) generates the average of the values when the contition is true. The first condition means "today" and the second one means "yesterday".

This is called a pivot operation. It is, as you see, quite a kludge in MySQL.

CodePudding user response：

SQL UNION requires the same amount of columns and mix them into a result set, but it uses the column names of the first select, even if the columns names are different to the others, this is why you only have 'today'.

UNION will return all the lines of each SELECT result into the same result set, but not necessarily into only one line. To achieve what you want, you should try this instead:

SELECT
name, 
(select AVG(st.price) from sales st where st.date = (CURRENT_DATE()) and st.name = s.name group by st.name) as today, 
(select AVG(sy.price) from sales sy where sy.date = (CURRENT_DATE() - INTERVAL 1 DAY) and sy.name = s.name group by sy.name) as yesterday 
FROM sales s
group by s.name

Feel free to add DATE(FROM_UNIXTIme()) as you typed.

It doesn't make sense print id and date with aggregation in this case.

English is not my first language, if you don't understand something, ask me again.

I suggest you to read this: https://www.w3schools.com/sql/sql_union.asp