How to get the sales per day in xano

haxxanali512

So I have a requirement where I want to fetch the sales per day which can easily be doable in sql query. I want to know if there is any way of doing it in xano based on created at. I want to get the response of each day sales.
Anyone here for help?

Pawel Magdanski

Do you need a list of sales or some aggregated data like sum, number of sales etc? If the second one then from the top of my head I can think of adding another field with date tranformed to readable format of your choice (d-m-Y for example) and then I would use query with aggregate type grouped by that dates and aggregated by whatever measure you need.

Not sure if this is the best solution nor if it applies to your situation specifically, can you provide some more details?

haxxanali512

Actually I have joined 3 tables and did some calculations like total products, sold products and remaining products. Now, I want to get the total of these values based on day. Like suppose I want to know how much sales we had in 2nd of may and how much on 1st of may. The purpose of getting the list is I want to make a graph of sales based on every day of month.

Pawel Magdanski

Then I would still create a field in table to categorize a day instead of a exact time you have in create_at so you don't have to calculate them on the fly while going throught the records.

How many days back do you need?

haxxanali512

From the first day of the app. I completed the app in last month. So from the first day to till date.

Pawel Magdanski

So if you want to apply it to all your data and transform all you records to be grouped in lists by days all the way back

Again from the top of my head head if you still need to have a table with every record separately you can create additional table with day [date] and list of relations to sales table. then every time you are creating a sale record you are checking if day record for that day already exist if not then you are creating on and adding sale record to it as a first one, if it exists then you are just adding it to the existing list for that day.

This way you don't need to calculate and group all of your records on the fly everytime you want to call it.

haxxanali512

I think the issue will still same. I have to give a date for which I can fetch the data. This can be done in current table as well. I tried with grouped by aggregate function but I don't know how can I request it for every day. The response should be an array of json objects. And one json object will represent one day.

Pawel Magdanski

That's exactly what this additional table will do. You will have a day (d-m-Y) and every time you are adding a sale record you are also checking if "now" transformed to d-m-Y and add this record ID to the respective list in the second table.

What will be the result is a table:
[date], [list of sales records ids]
03-05-2023, [1, 2, 3, 5]
04-05-2023, [14, 22, 31, 53]
…

Then you can query this table and use addons to bring the sales data via sales records ids