CODEWITHSUNDEEP
sqlsql-servergroup-bycount
StoledInk
StoledInk

Reputation: 174

How to select multiple columns, sum one column and group by multiple columns

I have a table named example, with columns user_id,date_start, and activity

I need to select user_id, date_startcolumns and count unique user_id and then group by user_id and date_start.

Table Data:

  ----------------------------------
 | user_id | date_start | activity  |
 |---------|------------|-----------|
 |  1      |2021-04-01  | CATIA     |
 |  1      |2021-04-05  | CATIA     |
 |  1      |2021-04-02  | CATIA     |
 |  1      |2021-05-01  | CATIA     |
 |  1      |2021-05-02  | CATIA     |
 |  3      |2021-05-02  | CATIA     |
 |  3      |2021-05-03  | CATIA     |
 |  4      |2021-05-05  | CATIA     |
  ----------------------------------

This Query:

SELECT FORMAT(d.date_start, 'yyyy-MM'), d.user_id
from (select d.user_id,  date_start,
         count(*) over (partition by user_id) as cnt,
         row_number() over (partition by FORMAT(date_start, 'yyyy-MM') order by FORMAT(date_start, 'yyyy-MM') desc) as seqnum
  from planner d
 ) d
where seqnum = 1;

I need my code show like this:

  ---------------------
 | date_start | total  |
 |------------|--------|
 | 2021-04    | 1      |
 | 2021-05    | 3      |
  ---------------------

Upvotes: 1

Views: 184

Answers (1)

eshirvana
eshirvana

Reputation: 24633

are you looking for this? :

select FORMAT(d.date_start, 'yyyy-MM') date_start
     , count(distinct user_id) total
from planner d
group by FORMAT(date_start, 'yyyy-MM')

Upvotes: 1

Related Questions