CODEWITHSUNDEEP
sqlsql-server
Suzy
Suzy

Reputation: 103

Calculate difference between two dates and count the number of occurrences

I am trying to calculate the difference between two dates and in the same query trying to count the frequency of occurence.

SELECT DATEDIFF(day,[Date1],[Date2]) AS 'Mydate'
     , count ([Mydate])    
  FROM Table1
 group 
    by 'Mydate'

I am expecting the query to calculate and count but getting an error msg "Each GROUP BY expression must contain at least one column that is not an outer reference." what I am expecting is, something like this:

Mydate  Count
0        5
1        4
2        5

Can someone help me please? Thanks

Upvotes: 2

Views: 4743

Answers (2)

McNets
McNets

Reputation: 10827

You cannot group by an alias:

SELECT 'Mydate', Cnt
FROM   (SELECT DATEDIFF(day, [Date1], [Date2]) AS 'Mydate', 
               Count(*) Cnt
        FROM   Table1) T1
GROUP BY 'Mydate'

Upvotes: 1

Rahul
Rahul

Reputation: 77936

First of all this doesn't look like MySQL rather SQL Server. Secondly you can't use the alias in Count() function like that. Rather say count(*)

  SELECT DATEDIFF(day,[Date1],[Date2]) AS 'Mydate', 
         Count (*)    
    FROM Table1
GROUP BY DATEDIFF(day,[Date1],[Date2])

Upvotes: 2

Related Questions