SQL - Dates and SUM

[Tim_S]

SELECT s.fullname AS userfullname,SUM(o.numberdays) AS daystotal, 
o.datefrom from occasions o INNER JOIN tra.staff s ON o.userid=s.id GROUP BY 
o.userid HAVING DATE_SUB(curdate(),INTERVAL 1 MONTH) <= `datefrom`

 

Quick question related to the code above:

 

I'm trying to add up the contents from a column (the SUM part) from a certain date range - in this case one month.

My problem is that it does the sum calculation first, so whatever date range I put in it will add all the contents of the columns - any suggestions?

 

T

Edited February 9, 2010 by Tim_S

[vikpaw]

i'm no whizz, but i recall that you can pass a select statement inside a function in order for it to act on the results, or did i just make that up.

 

so you could go sum(select o.numberdays . . . )

[Tim_S]

Thanks for the direction, will give that a try at least...

[Tim_S]

Tried that to no avail (doesn't seem to work with SUM as subqueries can only return 1 row)

 

Think I might have to try creating temp tables.

[tom_newton]

1. What database? Looks mysql-y

2. Give us a select * limit 5 from the tables

3. why "having" and not "where"?

 

I will try and have a look when I get home... too busy in the office today :'(

[Tim_S]

1. What database? Looks mysql-y

2. Give us a select * limit 5 from the tables

3. why "having" and not "where"?

 

I will try and have a look when I get home... too busy in the office today :'(

 

Good questions.

 

MySQL. It's a custom one for work, so not trying to extract from SIMS or anything.

 

Occasions

---------

id, userid, datefrom, dateto, numberdays, type, reason, note

"10","1","2010-02-02","2010-02-03","1.00","selfcert","sickness","ytjfhtht"

"11","1","2010-02-05","2010-02-09","5.00","selfcert","sickness","rthtrhrthtrh"

"12","1","2009-11-03","2009-11-12","9.00","selfcert","sickness","theasd"

"13","1","2008-12-17","2008-12-18","1.00","selfcert","sickness","2654yt5"

"14","344","2010-02-17","2010-02-18","2.00","selfcert","sickness","rthrthrt"

 

the staff table you only need to know they have fullname and id.

 

 

Usig HAVING as it's a group by clause - for use with SUM(). As far as I know that's the only way it could work.

 

Cheers,

T

[Tim_S]

Sorted it. Went the temporary tables route in the end:

CREATE TEMPORARY TABLE temp_occ (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a), KEY(id)) select `id`, `userid`, `datefrom`, `dateto`, `numberdays` from occasions WHERE DATE_SUB(curdate(),INTERVAL 1 YEAR) <= `datefrom`;
SELECT s.fullname AS userfullname,SUM(o.numberdays) AS daystotal, 
o.datefrom from temp_occ o INNER JOIN tracking.staff s ON o.userid=s.id GROUP BY 
o.userid

 

The temporary table creation pulls out the correct date range, then all I need to do is SUM() the dates.

 

Thanks for the input, it helped guide me in the right direction.

 

T

[vikpaw]

) glad it's sorted

[MattMitchell]

Good questions.

 

 

Usig HAVING as it's a group by clause - for use with SUM(). As far as I know that's the only way it could work.

 

Cheers,

T

 

You can use WHERE as well you know! It goes after the FROM clause, and before the GROUP BY clause

 

e.g.

 

SELECT aggregated_absence_data

FROM staff_absences_list

WHERE absence_date > last_year

GROUP BY staff_id

HAVING absencecount > 3

 

if you get my drift.

 

You use the WHERE clause to restrict the data before it gets near the aggregate functions. That said, it's a few years since I used MySQL so it may be an MS-Only syntax. Don't we love those.