count grouped records by ID and show as weekly with day/week outputted-CodePudding

I have a table in which I have to count total records assigned to each USER by weekly (monday to sunday).

Table BooksIssued

BOOKID      USER    DATE
 1            A        20211001
 2            A        20211002
 3            A        20211003
 4            A        20211004
 5            B        20211009
 6            C        20211008
 7            C        20211008

20211001 is friday.

output of sql query is as follows, the WEEKDATE column shows the week end date (i.e sunday)

WEEKCOUNT     USER     WEEKDATE
 3            A        10/03
 1            A        10/10
 1            B        10/10
 2            C        10/10

I am unable to get the date in output containing day, as grouping is done based on user and week part of date. Please suggest on getting above output. I am using vertica DB. Below is sample query i tried (though i could not get the day part of date)

SELECT USER, date_part('WEEK', date)) as WEEKDATE
       SUM(CASE WHEN DATE >= timestampadd(WEEK, DATEDIFF(WEEK, date('1900-01-01 00:00:00.000'), date(sysdate)), date('1900-01-01 00:00:00.000'))
                AND  DATE <  timestampadd(WEEK, DATEDIFF(WEEK, date('1900-01-01 00:00:00.000'), date(sysdate))   1, date('1900-01-01 00:00:00.000'))
                THEN 1 ELSE 0 END) AS WEEKCOUNT,
FROM   BOOKSISSUED
GROUP BY USER, date_part('WEEK', date)

when i add date_part('DAY', date) in select clause, i get error as its not in group by. Please help.

CodePudding user response：

Do you mean this?

WITH
-- your input ...
indata(BOOKID,USR,DT) AS (
          SELECT 1,'A',DATE '20211001'
UNION ALL SELECT 2,'A',DATE '20211002'
UNION ALL SELECT 3,'A',DATE '20211003'
UNION ALL SELECT 4,'A',DATE '20211004'
UNION ALL SELECT 5,'B',DATE '20211009'
UNION ALL SELECT 6,'C',DATE '20211008'
UNION ALL SELECT 7,'C',DATE '20211008'
)
SELECT
  COUNT(*) AS         week_count
, usr 
, TO_CHAR(
    DATE_TRUNC('WEEK',dt)   INTERVAL '6 DAYS'
  , 'MM/DD'
  )  AS trcweek
FROM indata
GROUP BY 2,3 
ORDER BY 2,3 
;
 week_count | usr | trcweek 
------------ ----- ---------
          3 | A   | 10/03
          1 | A   | 10/10
          1 | B   | 10/10
          2 | C   | 10/10

CodePudding user response：

Can you please check the sql query syntax.

In the SELECT clause second column and group by clause second column

SELECT USER, date_part('WEEK', date) as WEEKDATE,
   SUM(CASE WHEN DATE >= timestampadd(WEEK, DATEDIFF(WEEK, date('1900-01-01 00:00:00.000'), date(sysdate)), date('1900-01-01 00:00:00.000'))
            AND  DATE < timestampadd(WEEK, DATEDIFF(WEEK, date('1900-01-01 00:00:00.000'), date(sysdate))   1, date('1900-01-01 00:00:00.000'))
            THEN 1 ELSE 0 END) AS WEEKCOUNT
FROM BOOKSISSUED
GROUP BY USER, date_part('WEEK', date)