How many items sold each hour for each UID?-CodePudding

I have a products table with the below structure (limited to 10 rows)

 UID                                      Time           Name Total_Amount
('a63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740389, 'test',1.9)
('a63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740390, 'test 1',1.9)
('b63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740391, 'test 2',2.1)
('b63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740392, 'test 3',2.3)
('c63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740393, 'test 4',3.5)
('c63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740394, 'test 5',4.9)
('d63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740395, 'test 6',5.3)
('d63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740396, 'test 7',6.7)
('e63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740397, 'test 8',8.1)
('e63d7d52-0fa8-4d56-b954-a9ecb86e350a', 1599887740398, 'test 9',9.7)

I need to write a query to find out "How many items were sold for each hour of the day for each UID?"

I tried

SELECT UID,time,count(total_amount) from products group by UID,Time

but this just outputs the list, How can I get a count of how many items were sold of each UID for each hour?

CodePudding user response：

You can strip minutes and seconds from the timestamps if you divide by 3600000 and multiply by 3600, and then aggregate per product and hour:

SELECT UID, 
       datetime(Time / 3600000 * 3600, 'unixepoch') hour,
       SUM(Total_Amount) Total_Amount
FROM products
GROUP BY UID, hour;

See the demo.

CodePudding user response：

Your time looks like unix milliseconds. Try

select UID, strftime('%Y-%m-%d %H',time/1000, 'unixepoch') h, count(total_amount) 
from products 
group by UID, strftime('%Y-%m-%d %H',time/1000, 'unixepoch')