Missing records in N1ql Query

Hello

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


[
  {
    "day": "Monday",
    "total_bill": 937.17
  },
  {
    "day": "Tuesday",
    "total_bill": 1313.5000000000002
  },
  {
    "day": "Wednesday",
    "total_bill": 1708.2700000000002
  },
  {
    "day": "Sunday",
    "total_bill": 1018.6800000000001
  }
]

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);

you have not mentioned any bucket ref plus its returning a wired response. its returning all days but total_bill is same for all


[
  {
    "day": "Saturday",
    "total_bill": 13
  },
  {
    "day": "Sunday",
    "total_bill": 13
  },
  {
    "day": "Monday",
    "total_bill": 13
  },
  {
    "day": "Tuesday",
    "total_bill": 13
  },
  {
    "day": "Wednesday",
    "total_bill": 13
  },
  {
    "day": "Thursday",
    "total_bill": 13
  },
  {
    "day": "Friday",
    "total_bill": 13
  }
]

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"

When i run this query

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"

The response is

[
  {
    "$1": 1018.6800000000001
  }
]

but above query is giving me in sunday is 13

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 ?

Thanks

Your original query is fine. There is no issue.

1 Like