List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 5 2006 5:28pm
Subject:Re: very long query for such a simple result
View as plain text  
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
>   

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