List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 6 2006 12:29pm
Subject:Re: very long query for such a simple result
View as plain text  
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
Thread
very long query for such a simple resultEd Reed4 Apr
  • Re: very long query for such a simple resultSGreen4 Apr
  • Re: very long query for such a simple resultPeter Brawley4 Apr
Re: very long query for such a simple resultEd Reed4 Apr
Re: very long query for such a simple resultEd Reed4 Apr
  • Re: very long query for such a simple resultPeter Brawley5 Apr
  • Re: very long query for such a simple resultsheeri kritzer5 Apr
Re: very long query for such a simple resultEd Reed5 Apr
  • Re: very long query for such a simple resultPeter Brawley5 Apr
Re: very long query for such a simple resultEd Reed6 Apr
  • Re: very long query for such a simple resultBarry6 Apr
  • Re: very long query for such a simple resultPeter Brawley6 Apr