I have a 'sales' table called phpbb_sold which records each 'sale' as a row.
I am able to use a WHERE clause with the uitemid field to select one particular item in the sales records, as seen below:
SELECT uitemid, locktime, migrated_sold FROM phpbb_sold WHERE uitemid=342;
--------- ------------ ---------------
| uitemid | locktime | migrated_sold |
--------- ------------ ---------------
| 342 | 1632523854 | 1 |
| 342 | 1634239244 | 1 |
| 342 | 1634240072 | 1 |
| 342 | 1636367271 | 1 |
--------- ------------ ---------------
uitemid = number that identifies this as a sale of X item. locktime = UNIX timestamp that shows the datetime that the item was sold. migrated_sold = the quantity of the item sold. So this is nice, I have a table that keeps a record of each sale as it happens.
What I want to achieve though, is a record of the total number of sales of this item type, for each day in a 6 month period spanning back from the current date, and including each day regardless of whether a sale was made or not. So the desired output of my query would be:
SELECT (the query I want goes here) and returns the following rows...;
------------ ------------
| caldate | sold_total |
------------ ------------
| 2021-09-23 | 2 |
| 2021-09-24 | 0 |
| 2021-09-25 | 1 |
| 2021-09-26 | 0 |
| 2021-09-27 | 0 |
| 2021-09-28 | 1 |
------------ ------------
Note that each day is included as a row in the results, even where the sales total for that day is 0. I read that to do this, I would be required to create a calendar table with one column and all the days I want as rows, so I went ahead and did that:
SELECT caldate FROM phpbb_calendar;
------------
| caldate |
------------
| 2021-09-23 |
| 2021-09-24 |
| 2021-09-25 |
| 2021-09-26 |
| 2021-09-27 |
| 2021-09-28 |
------------
Now all that remains is for me to make the query. I need to somehow return all the rows from the phpbb_calendar table, joining the data from sum() (?) of the total migrated_sold for those days where exists, and a 0 where no sales took place.
I anticipated some issues with the UNIX timestamp, but it's okay because I am able to get caldate and locktime fields to be the same format by using from_unixtime(locktime, '%Y-%m-%d'), so both dates will be in the YYYY-MM-DD format for comparison.
Please could someone help me with this. I've gotten so close every time but it seems that everyone else's request is only slightly different from mine, so existing questions and answers have not been able to satisfy my requirements.
End goal is to use a JS chart library (AnyChart) to show a line graph of the number of sales of the item over time. But to get there, I first need to provide it with the query necessary for it to display that data.
Thanks
Update
Using this query:
SELECT c.caldate, u.uitemid, sum(v.migrated_sold) as total_sales
from phpbb_calendar c cross join
(select distinct uitemid from phpbb_sold) u left join
phpbb_sold v
on c.caldate = from_unixtime(v.locktime, '%Y-%m-%d') WHERE u.uitemid = 39 and c.caldate <= curdate() GROUP BY c.caldate ORDER BY c.caldate;
Returns:
But as you can see, it's just tallying up the total number of sales ever made or something - its clearly incrementing in a way I don't understand. I don't want it to do that - I want it to count the number of total sales on each day individually. The results should look like this:
So that what is returned is basically a 'histogram' of sales, if any occurred, including 'empty' days where there were no sales (so these empty days must still be returned as rows).
CodePudding user response：
check this out:
select id, d, sum(s) from (
select U.id, d, 0 s from (
select adddate(current_date(),-rows.r) d from (
select (@row_number := @row_number 1) r
from information_schema.columns,
(SELECT @row_number := 0) AS x
limit 200
) rows
) dates,
(SELECT distinct uitemid id FROM `phpbb_sold`) U
where d > adddate(current_date(), interval -6 month)
union
select uitemid, date(from_unixtime(locktime)),sum(migrated_sold)
from `phpbb_sold`
group by uitemid, date(from_unixtime(locktime))
) sales_union
group by id, d
order by id, d;
see dbfiddle no need for calendar table
CodePudding user response：
SELECT c.caldate, u.uitemid, SUM(v.migrated_sold) AS total_sales
FROM phpbb_calendar c
CROSS JOIN (SELECT DISTINCT uitemid FROM phpbb_sold) u
LEFT JOIN phpbb_sold v
ON v.locktime BETWEEN UNIX_TIMESTAMP(TIMESTAMP(c.caldate)) AND UNIX_TIMESTAMP(TIMESTAMP(c.caldate, '23:59:59'))
AND u.uitemid = v.uitemid
WHERE u.uitemid = 39
AND c.caldate BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE()
GROUP BY c.caldate
ORDER BY c.caldate;
N.B. I have changed your join to use the unix_timestamp as it should be more efficient and it can use any existing index on locktime