SQL average money spent per day

I would like to get some help in writing SQL query for the table below. The table represents how much students spent at a food court on a given date. With this table, write an SQL query that calculates the number of days between the first and last date of money spent by each student and the money spent per day in this timespan.

Student_id            Date      Money Spent($)
    1              01.01.2017      15.13
    2              03.01.2017      22.94
    3              03.01.2017      37.50
    2              01.02.2017      44.26
    2              08.04.2017      52.62
    3              09.08.2017      34.67
    1              02.09.2017      43.87
    3              04.11.2017      12.67

Answer

Try this

SELECT Student_id,MIN([date]) mindate, MAX([date]) maxdate,SUM(MoneySpent) TotalSpent,
        SUM(MoneySpent)/DATEDIFF ( MIN([date]) , MAX([date]) ) MoneySpentPerDay 
        FROM StudentExpenses
        GROUP BY Student_id

Advertisement

Answer