CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Cryssie
Cryssie

Reputation: 3175

Get daily count of users by id returning error

I am trying to get daily count of unique users from a SQL Server table.

An example of how the table would look like:

user_id start_timestamp     activity
111     08/25/2020 13:23    aaaa
111     08/25/2020 14:24    bbbb
222     08/25/2020 14:26    cccc
222     08/25/2020 14:28    dddd
333     08/25/2020 14:31    eeee

An example of the result table I am looking for:

user_id start_date  count
111     08/25/2020  2
222     08/25/2020  2
333     08/25/2020  1

This is what I have tried. '

SELECT 
    [user_id],  
    CONVERT(VARCHAR,[start_timestamp], 23) as [start_date]
    COUNT([user_id]) AS [user_count]
  FROM [dbo].[user_activity]
  GROUP BY [user_id],[start_date]
  ORDER BY [start_date]

I had to convert the date from timestamp to just dates for the group by but I keep getting an error message:

Invalid column name 'start_date'.

Upvotes: 0

Views: 64

Answers (4)

Charlieface
Charlieface

Reputation: 71374

You should not convert to varchar to get a date without a time. Instead convert to date.

As mentioned by others, you cannot reuse a select alias in the group by. You can repeat the same convert again, but a better way is to place it into a cross apply, this allows you to reuse it later.

You should also use proper table aliasing.

SELECT 
    u.user_id,  
    v.start_date
    COUNT(user_id) AS user_count
  FROM dbo.user_activity u
  CROSS APPLY (VALUES( CAST(u.start_timestamp AS date) ) v(start_date)
  GROUP BY u.user_id, v.start_date
  ORDER BY v.start_date

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35573

In a single select statement you aren't able to use the alias setup by the select clause in the group by clause.

SELECT 
    [user_id],  
    CONVERT(VARCHAR,[start_timestamp], 23) as [start_date] --<< established here
    COUNT([user_id]) AS [user_count]
FROM [dbo].[user_activity]
GROUP BY [user_id],[start_date] --<< illegal reuse of alias here
ORDER BY [start_date]

You can avoid this by simply repeating the information before the alias, like this

SELECT 
    [user_id],  
    CONVERT(VARCHAR,[start_timestamp], 23) as [start_date] --<< established here
    COUNT([user_id]) AS [user_count]
FROM [dbo].[user_activity]
GROUP BY [user_id],CONVERT(VARCHAR,[start_timestamp], 23) --<< no alias here
ORDER BY [start_date]

I would prefer to see you cast the timestamp to a date instead of converting to a string.

SELECT 
    [user_id],  
    cast([start_timestamp] as date) as [start_date] --<< alias established here
    COUNT([user_id]) AS [user_count]
FROM [dbo].[user_activity]
GROUP BY [user_id],cast([start_timestamp] as date)
ORDER BY [start_date]

Upvotes: 1

jarlh
jarlh

Reputation: 44766

The column alias [start_date] is created after the GROUP BY is evaluated.

Instead use a derived table (the subquery) to first get the [start_date], and then GROUP BY.

SELECT 
    [user_id],  
    [start_date]
    COUNT([user_id]) AS [user_count]
FROM
(
  SELECT 
      [user_id],
      CONVERT(VARCHAR,[start_timestamp], 23) as [start_date]
  FROM [dbo].[user_activity]
) dt
GROUP BY [user_id], [start_date]
ORDER BY [start_date]

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

you need to add the converted date column to grıup by section.

SELECT 
    [user_id],  
    CONVERT(VARCHAR,[start_timestamp], 23) as [start_date]
    COUNT([user_id]) AS [user_count]
  FROM [dbo].[user_activity]
  GROUP BY [user_id],CONVERT(VARCHAR,[start_timestamp], 23)
  ORDER BY [start_date]

Upvotes: 1

Related Questions