how to count number of records from 3rd day to 62nd day for each person by using SQL query?

Question

I want to get the count of 'unitsold' for each Name from 3/1/2022 and 4/1/2022. For example Name 'a' required to count its units sold from 3rd to 8th day.

SQL query:

select Name, sum(unitsold) over (partion by Name order by Asc) from table1

The above query will provide units sold for the entire date range. But how to get sum of units sold from 3rd day to 8th day of each name?

You can use subquery or analytic sum with [Window Function Frame Specification](https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html). In this case something like this [dbfiddle](https://dbfiddle.uk/NAX83aYD). — Ponder Stibbons, Apr 17 '23 at 12:31

score 0 · Answer 1 · answered Apr 16 '23 at 08:47

0

Select name, sum (CASE when date >= '22/03/03' AND date <='22/03/08' then unitsold else 0 end) as totalUnitsSold
From table1 where 
Date >='22/03/01' and Date <='22/03/01'
Group by Name;

The case handles day logic and where clause handles the date range of 1 month.

Hope this helps, Thanks.

answered Apr 16 '23 at 08:47

Suchandra T

569
4
8

This is simplest query and limit to the date in where condition. I need to get the result for roll on basis. The where condition is for names exist in 22/03/01. But on the next day again some new names and units sold count from 22/03/03 to 22/03/08 and so on continuous for 6 months data. How the same logic applied for new names comes in next 6 months ? – Sanky Ach Apr 17 '23 at 09:54

score 0 · Answer 2 · answered Apr 17 '23 at 14:08

0

This is the query which gives the out put.

answered Apr 17 '23 at 14:08

Sanky Ach

333
8
23

how to count number of records from 3rd day to 62nd day for each person by using SQL query?

2 Answers2