How how count rows between two dates in SQL?

Question

I have a table with columns index(sometimes changes to B) ,price and pricedate. I want to add column counts, which shows the number of working day in the specific month

I tried following code, however it shows that the column price should be also grouped. I tried select without price column, however in counta column I got only 1 for each of the dates. Do you know how to fix the problem and have the result as in the table above?

select priceindex, price, pricedate ,count(pricedate) as counta --, price
from myDB
and pricedate between date_trunc('month',pricedate) and pricedate + interval '1 day'
group by index, pricedate

How do you know what days are working days? Are you just enumerating the rows or is there something fancier going on? — Gordon Linoff, Jul 29 '20 at 13:45

Hank Warner · Answer 1 · 2020-07-29T15:19:19.700

If the pricedate data has no duplicates, you don't need the group by. If there are duplicate values for pricedate, then you DO need a group by, but the question is: what do you want to happen to the price when you group by pricedate, since there may be 2 different prices for the same date? e.g.

price  pricedate
6      06.05.2020
4      06.05.2020

in this scenario, which price do you want, 4 or 6? A good answer might be the average:

select index, avg(price), pricedate, count(pricedate) as counta
from myDB
where pricedate between date_trunc('month',pricedate) and pricedate + interval '1 day'
group by index, pricedate

Other options are min(price), max(price) etc. If all the prices are the same, then min() max() and avg() will return the same value> Some databases have first() which returns a value from any row the database considers to be 'first' (and depending on the database, this may not be helpful). --edit-- If you use count(pricedate) and 'group by pricedate' then you will only ever get a count of 1. Perhaps you have misunderstood the 'between' clause (which is used to limit data, not to calculate the difference between dates). try

select (pricedate - date_trunc('month',pricedate)) as counta

(no group by at this time) and see if that gives you a closer answer.

Hello, I have unique dates. The only variable that should be grouped by your logic is index (here it shows only A, in reality A:D). And the same as in the previous comment, unfortunately, I have only 1 values in column counta. — John Castello, Jul 29 '20 at 13:24

score 1 · Answer 2 · answered Jul 31 '20 at 12:30

Always bring your data as plain text, not in an image, so we all can copy/paste.

I took a total of 15 minutes to re-type your input data and re-format it.

But, now I have it:

What you need is the running sum of: 1 if it's a week day, 0 if it's a week-end-day, per month. That's plain vanilla OLAP , window function, functionality. Welcome to SQL-99 ...

Here goes.

The input:

WITH
-- your input as in-line table, typed manually ...
input(index,price,pricedate,counta_in) AS (
          SELECT 'A',NULL::INT,DATE '2020-05-01', 1
UNION ALL SELECT 'A',        5,DATE '2020-05-04', 2
UNION ALL SELECT 'A',        4,DATE '2020-05-05', 3
UNION ALL SELECT 'A',        6,DATE '2020-05-06', 4
UNION ALL SELECT 'A',        4,DATE '2020-05-07', 5
UNION ALL SELECT 'A',        4,DATE '2020-05-11', 6
UNION ALL SELECT 'A',        6,DATE '2020-05-12', 7
UNION ALL SELECT 'A',        7,DATE '2020-05-13', 8
UNION ALL SELECT 'A',        8,DATE '2020-05-14', 9
UNION ALL SELECT 'A',        8,DATE '2020-05-15',10
UNION ALL SELECT 'A',        8,DATE '2020-05-18',11
UNION ALL SELECT 'A',        4,DATE '2020-05-19',12
UNION ALL SELECT 'A',        5,DATE '2020-05-20',13
UNION ALL SELECT 'A',        3,DATE '2020-05-21',14
UNION ALL SELECT 'A',        5,DATE '2020-05-22',15
UNION ALL SELECT 'A',        4,DATE '2020-05-26',16
UNION ALL SELECT 'A',        5,DATE '2020-05-27',17
UNION ALL SELECT 'A',        3,DATE '2020-05-28',18
UNION ALL SELECT 'A',        5,DATE '2020-05-29',19
UNION ALL SELECT 'A',        4,DATE '2020-06-01', 1
UNION ALL SELECT 'A',        6,DATE '2020-06-02', 2
UNION ALL SELECT 'A',        4,DATE '2020-06-03', 3
UNION ALL SELECT 'A',        4,DATE '2020-06-04', 4
UNION ALL SELECT 'A',        5,DATE '2020-06-05', 5
UNION ALL SELECT 'A',        4,DATE '2020-06-08', 6
UNION ALL SELECT 'A',        6,DATE '2020-06-09', 7
)

The actual select, selecting from the input above:

-- acutal query starts here. counta_out is my OLAP expression.
SELECT
  *
, SUM(CASE WHEN DAYOFWEEK_ISO(pricedate) < 6 THEN 1 ELSE 0 END)
  OVER(PARTITION BY MONTH(pricedate) ORDER BY pricedate) 
  AS counta_out
FROM input;

And the result, including control column and result column:

index | price | pricedate  | counta_in | counta_out 
------+-------+------------+-----------+------------
A     |       | 2020-05-01 |         1 |          1
A     |     5 | 2020-05-04 |         2 |          2
A     |     4 | 2020-05-05 |         3 |          3
A     |     6 | 2020-05-06 |         4 |          4
A     |     4 | 2020-05-07 |         5 |          5
A     |     4 | 2020-05-11 |         6 |          6
A     |     6 | 2020-05-12 |         7 |          7
A     |     7 | 2020-05-13 |         8 |          8
A     |     8 | 2020-05-14 |         9 |          9
A     |     8 | 2020-05-15 |        10 |         10
A     |     8 | 2020-05-18 |        11 |         11
A     |     4 | 2020-05-19 |        12 |         12
A     |     5 | 2020-05-20 |        13 |         13
A     |     3 | 2020-05-21 |        14 |         14
A     |     5 | 2020-05-22 |        15 |         15
A     |     4 | 2020-05-26 |        16 |         16
A     |     5 | 2020-05-27 |        17 |         17
A     |     3 | 2020-05-28 |        18 |         18
A     |     5 | 2020-05-29 |        19 |         19
A     |     4 | 2020-06-01 |         1 |          1
A     |     6 | 2020-06-02 |         2 |          2
A     |     4 | 2020-06-03 |         3 |          3
A     |     4 | 2020-06-04 |         4 |          4
A     |     5 | 2020-06-05 |         5 |          5
A     |     4 | 2020-06-08 |         6 |          6
A     |     6 | 2020-06-09 |         7 |          7

score 0 · Answer 3 · answered Jul 29 '20 at 13:16

When you use clause GROUP BY, you need to specify all columns used in SELECT. Then the query is

SELECT priceindex, price, pricedate, count(pricedate) AS counta
FROM myDB
WHERE pricedate BETWEEN date_trunc('month',pricedate) and pricedate + interval '1 day'
GROUP BY priceindex, price, pricedate

You can also try the clause DISTINCT instead of GROUP BY.

SELECT DISTINCT priceindex, price, pricedate, count(pricedate) AS counta
FROM myDB 
WHERE pricedate BETWEEN date_trunc('month',pricedate) and pricedate + interval '1 day'

Thank you for your reply. I added price column, however, in the end, I have the same result that in column counts all numbers are 1 — John Castello, Jul 29 '20 at 13:22

score 0 · Answer 4 · answered Jul 29 '20 at 13:31

0

SELECT priceindex, price, pricedate
        , row_number() OVER
                (PARTITION BY priceindex, date_trunc('month', pricedate)
                ORDER BY pricedate) AS counta 
FROM myDB
        ;

answered Jul 29 '20 at 13:31

wildplasser

43,142
8
66
109

How how count rows between two dates in SQL?

4 Answers4