CODEWITHSUNDEEP
sqldatetimesaverowcount
Cheri Choc
Cheri Choc

Reputation: 141

sql save row count of query every day

I want to create a view or table that counts eg. total number of students on that day the query is executed and add row results each day. Problem is the date column on table changes everyday to the current date.

SELECT
     COUNT(*) AS no_of_Students
     ,CAST(GETDATE() AS DATE) as DATE
FROM mySchool 
WHERE students=1

No of student  .  Date
-----------------------
8              .  2019.02.06
15             .  2019.02.07

(next row should auto update for next day when running the query)

Upvotes: 0

Views: 607

Answers (1)

PSK
PSK

Reputation: 17943

You should not be using GETDATE(), You need to pick the date column you have in your mySchool table.

You need to write your query like following.

SELECT
COUNT(*) as [no_of_Students]
,CAST([DateCoulumn] AS DATE) as [DATE]
FROM [mySchool]
GROUP BY CAST(DateCoulumn AS DATE)
ORDER BY CAST(DateCoulumn AS DATE)

Note: You need to replace DateCoulumn with the correct column name.

Upvotes: 0

Related Questions