Sql query help, grabbing dates, counts, averages

Question

I have a table called payhistory, and from this table I need to grab the Year, Month when a payment was made. I then need to get an average of payments grouped by Year and then month.

select AVG(totalpaid) from payhistory

This table has a column called datepaid that is where I need to grab the date from. It has a column called totalpaid which is where I need to grab the average from.

I then need a count of every payment within that month.

And finally a column displaying which Year/Month it was from.

Important columns in the table:

number (account number can use this to get a count)
datepaid (contains the date the payment was entered '2009-09-28 00:00:00.000')
totalpaid (contains the specific payment for that date)

@marc_s number would be int, datepaid datetime and toldpaid should be money. — James Wilson, Oct 16 '13 at 20:55

score 5 · Accepted Answer · answered Oct 16 '13 at 20:01

5

This should work:

select AVG(totalpaid),
   COUNT(number),
   MONTH(datepaid),
   YEAR(datepaid)
from payhistory
group by 
   MONTH(datepaid),
   YEAR(datepaid)

answered Oct 16 '13 at 20:01

sgeddes

62,311
6
61
83

Is it possible to make the first column say the Year/Month name? – James Wilson Oct 16 '13 at 20:07
1

@JamesWilson -- Assuming you want to display the month name and not the month number, see this post: http://stackoverflow.com/a/188390/2480596 – sgeddes Oct 16 '13 at 20:09

Sql query help, grabbing dates, counts, averages

1 Answers1