I am trying to get the sum of total bills on given day (Monday,Tuesday etc) . The query i am using to fetch the records based on days is
SELECT WEEKDAY_STR(invoice.date) AS day , SUM(invoice.total_bill) AS total_bill FROM st_data_bucket invoice
WHERE invoice.date BETWEEN '2022-06-01' AND '2022-08-31' AND invoice.store_id = '248' AND invoice.type = 'invoice' GROUP BY WEEKDAY_STR(invoice.date)
Query is working fine and giving me the result as well but the problem is it misses some days .
For Example i have sales on Friday as well between Jun to Aug but its misses that here is the results
checkout store_id, type those might not matched and eliminated.
Run this and see if you get all 7 days
SELECT WEEKDAY_STR(invoice.date) AS day , SUM(invoice.total_bill) AS total_bill
FROM (ARRAY {"date":d, "store_id":"248", "type":"invoice", "total_bill" :1 } FOR d IN DATE_RANGE_STR("2022-06-01","2022-08-31", "day") END) AS invoice
WHERE invoice.date BETWEEN '2022-06-01' AND '2022-08-31' AND invoice.store_id = '248' AND invoice.type = 'invoice'
GROUP BY WEEKDAY_STR(invoice.date);
It is not wired it correct response. I have generated data in the query dynamically every day one record. The query is giving right results. It proves all of them working correctly.
You need to check your data and debug and see if you have data on Friday.
example:
SELECT invoice.* FROM st_data_bucket invoice
WHERE invoice.date BETWEEN '2022-06-01' AND '2022-08-31'
AND invoice.store_id = '248'
AND invoice.type = 'invoice'
AND WEEKDAY_STR(invoice.date) = "Friday"
SELECT SUM(invoice.total_bill) FROM st_data_bucket invoice
WHERE invoice.date BETWEEN '2022-06-01' AND '2022-08-31' AND invoice.store_id = '248' AND invoice.type = 'invoice' AND WEEKDAY_STR(invoice.date) = "Sunday"
One i posted query to prove query gives right groups not missing as you said.
Your query uses data from bucket. One i posted uses expression chcek FROM clause.
FROM st_data_bucket
vs
FROM (ARRAY {"date":d, "store_id":"248", "type":"invoice", "total_bill" :1 } FOR d IN DATE_RANGE_STR("2022-06-01","2022-08-31", "day") END) AS invoice
As you saying Friday is missing, run this and see what you get.
SELECT invoice.* FROM st_data_bucket invoice
WHERE invoice.date BETWEEN '2022-06-01' AND '2022-08-31'
AND invoice.store_id = '248'
AND invoice.type = 'invoice'
AND WEEKDAY_STR(invoice.date) = "Friday"
OR
SELECT invoice.* FROM st_data_bucket invoice
WHERE invoice.date IN [ "2022-06-03", "2022-06-10", "2022-06-17", "2022-06-24", "2022-07-01", "2022-07-08", "2022-07-15", "2022-07-22", "2022-07-29", "2022-08-05", "2022-08-12", "2022-08-19", "2022-08-26" ]
AND invoice.store_id = '248'
AND invoice.type = 'invoice'
What i needed was all week days sum of total bills from the the given date for example from 01-06-2022 to 01-08-2022 . My orignal query was missing friday but you are right i dont any any records on friday in this period because i have started the app on 28 aug but when i extended the range from 01-06-2022 to 31-09-2022 it works fine .
SELECT WEEKDAY_STR(invoice.date) AS day , SUM(invoice.total_bill) AS total_bill FROM st_data_bucket invoice
WHERE invoice.date BETWEEN '2022-06-01' AND '2022-09-31' AND invoice.store_id = '248' AND invoice.type = 'invoice' GROUP BY WEEKDAY_STR(invoice.date)
this seems to be working now and giving me response quickly as well . Do i need expression or what i am doing is fine ?