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@earthlink.net> 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@earthlink.net> 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(*) IN
TO @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 WHER
E (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 (ProblemRep
orts.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