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.
Thanks again.
>>> Peter Brawley <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL
PROTECTED]> 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
