List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 9 1999 4:11pm
Subject:Re: PLEASE HELP! Newbie Question about SUM function in GROUP BY clause
View as plain text  
OTR Comm wrote:
> 
> Hello,
> 
> I have a question!
< ... > 
> This script loops through all of my usernames and filters the database
> with this query (wrapped here to fit into this mail window):
> 
> $SQL = "select *  from user_records where user_name = \"$username\" &&
> $insertquery order by sess_year, mon, user_name, daynum, sess_time";
> 
> NOTE: The variable $insertquery here is a dynamically built part of the
> query to allow me to select multiple months to develop reports for.
> When I am looking for users over 200 hours, it is a single, selected
> month to check.
> 
> Then I go through each of the resultant records (each one being a
> separate 'stop record') and add the time logged on for that session into
> an accumulator variable in perl.  After I am through with this user's
> records, I check to see if the accumulator variable is over 200, and
> flag this user if it is.  Pretty simple!
< ... > 
> Is there a way to filter the database with a username, use the SUM
> function of the GROUP BY clause, and either store the results in a field
> in the database that I can access through perl *or* access the results
> of the SUM function in perl and store it in a variable in my script and
> then work with it?  The first option would be best, if possible.  Then
> each time I updated my database, I would just need to update this field
> and then query this field value for being over 200.
> 
> I have never written a function for MySQL, but I guess it could be done
> this way, huh?
> 
> I really would appreciate some help with this.
> 
> Please reply via e-mail to otrcomm@stripped!
> 
> Thanks In Advance,
> Murrah Boswell

Hi Murrah

Why don't you just use this SELECT instead?
SELECT
	user_name
	, SUM( sess_time ) AS total_time
FROM
	user_records
WHERE
	$insertquery
GROUP BY 
	user_name
HAVING
	total_time > 200
	;

Tschau
Christian

PS: Sorry for the late answer, I was really busy.

Thread
PLEASE HELP! Newbie Question about SUM function in GROUP BY clauseOTR Comm31 Aug
  • Re: PLEASE HELP! Newbie Question about SUM function in GROUP BY clauseChristian Mack9 Sep