Ed,
> That's not really what I meant. I know what the function SUM() does. But Sum() takes
> an expression and '1' doesn't seem like much of an expression to me. So what is that 1
> equates to and where in the MySQL documentation can I find this explained.
>
The expression here is '1', which it sums rowwise.
PB
-----
>
> Thanks again.
>
>
>>>> Peter Brawley <peter.brawley@stripped> 4/5/06 10:28 AM
> >>>
>>>>
>
> Hi Ed,
>
> Count(1) works just as well. Sum(1) just adds 1 for each row so it's logically
> equivalent.
>
> PB
>
> -----
>
> Ed Reed wrote: WOW!!! THAT WAS AWESOME!!! Thanks a lot Peter. Ok, so what is SUM(1)?
> How is it able to do this? And where can I learn more about it? Thanks again. Peter
> Brawley <peter.brawley@stripped> 4/4/06 10:13:00 PM >>> Ed,e:
> Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that I have to
> make work together and the problemreport table does not reference the employeeid in the
> employees table. It was all create about ten years ago and the data has just always been
> migrated to the db du jour. I'm currently using MySQL 4.1x but most importantly I must be
> able to run the entire query in a single call. Thanks for the help. OK, that's doable in
> a subquery, and you can get the total from SUM(1), so something like ...SELECT IF( SUM(1)
> = 0, '', CONCAT( 'You have ', SUM(1), ' Problem
> Report', IF(SUM(1) = 1,'','s'), ': Priorities(High=',
> SUM(IF(Priority='High',1,0)), ',Med=',
> SUM(IF(Priority='Med',1,0)), ',Low=', SUM(IF(Priority='Low'
> ,1,0)), ')' ) )FROM ( SELECT Priority FROM ProblemReports,
> Employees WHERE ProblemReports.Status='Open' AND
> ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName) AND
> Employees.DateTerminated IS NULL AND Employees.UserName='User1') AS priorities;PB-----
> Peter Brawley <peter.brawley@stripped> 4/4/06 2:35:49 PM >>>
> Ed,The big slowdown in your query is likely the join on ProblemReports.Responsible =
> CONCAT(Employees.FirstName, ' ',Employees.LastName)) Eek. Does the employees table not
> have a primary key, and does the problemreports table not reference that key? If not, I
> would think a first priority would be to fix that.Meanwhile, you can simplify your monster
> query by writing the user's problem priorities to a temp table, then doing a simple pivot
> on priority to generate your sentence. Something like ...DROP TEMPORARY TABLE IF EXISTS
> ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT Priority FROM ProblemReports, Employees WHERE
> ProblemReports.Status='Open' AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
> ',Employees.LastName) AND Employees.DateTerminated IS NULL AND
> Employees.UserName='User1'SELECT COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0,
> '', CONCAT( 'You have', @n, 'Problem Report',
> IF(@n=1,'','s'), ': Priorities(High=',
> SUM(IF(Priority='High',1,0)), ',Med=',
> SUM(IF(Priority='Med',1,0)), ',Low=', SUM(IF(Priority='Low'
> ,1,0)), ')' ) )FROM probsum;DROP TEMPORARY TABLE probsum;All
> this would be easier in a stored procedure, if you have MySql 5.PB -----Ed Reed wrote:
> Can someone help me simplify this query please? It's meant to return a single string
> result that looks something like this, "You have 12 open Problem Reports:
> Priorities(High=5, Med=6, Low=1)" The relavent columns from the two tables are Table:
> ProblemReportsFields: PRNo, Status, Priority, Responsible Table: EmployeesFields:
> Firstname, Lastname, DateTerminated, UserName Here's the query Select IF((@a:=(SELECT
> Count(PRNo) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
> (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
> (Employees.DateTerminated Is Null) AND (Employees.UserName='User1')))>0, If (@a=1,
> ConCat('You have one open Problem Report: Prioritiy = ', (Select Priority From
> ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
> (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
> (Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))), Concat('You have
> ', @a, ' open Problem Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
> ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
> (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
> (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
> (Priority='High')),', ',(Select ConCat('Med=',Count(Priority)) From ProblemReports,
> Employees WHERE (ProblemReports.Status='Open') AND
> (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
> (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
> (Priority='Med')),', ',(Select ConCat('Low=',Count(Priority)) From ProblemReports,
> Employees WHERE (ProblemReports.Status='Open') AND
> (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
> (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
> (Priority='Low')),')')),''); No virus found in this incoming message.Checked by AVG Free
> Edition.Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006 No
> virus found in this incoming message.Checked by AVG Free Edition.Version: 7.1.385 / Virus
> Database: 268.3.5/300 - Release Date: 4/3/2006 No virus found in this incoming
> message.Checked by AVG Free Edition.Version: 7.1.385 / Virus Database: 268.3.5/301 -
> Release Date: 4/4/2006
>
>
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.3.5/301 - Release Date: 4/4/2006
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/301 - Release Date: 4/4/2006
