Database sum - automate the search criteria instead of hard coding the value

[robin01]

Hello,

How do I automate the search criteria in the sum function, as of now I have the seach values hardcoded (sum(bin.tkt_status = ‘2’) as ‘Pending’). Looking to make it more dynamic so in the event another tkt_status is added its automatically reflected in the sql statement

	$query = $conn->prepare("SELECT bin.tkt_index, bin.tkt_priority, bin.tkt_status, 
                                                        bin.tkt_assignedTo,
							count(bin.tkt_priority) as countPriority,
							sum(bin.tkt_status = '2') as 'Pending',
							sum(bin.tkt_status = '3') as 'Resolved',
							sum(bin.tkt_status = '8') as 'Information',
							sum(bin.tkt_status = '9') as 'New',
							sum(bin.tkt_status = '10') as 'Hold',
							sum(bin.tkt_status = '11') as 'Scheduled'							
                          FROM tbl_ticketbin AS bin
						  JOIN tbl_priority AS p ON bin.tkt_priority = p.pry_index
						  JOIN tbl_status AS s ON bin.tkt_status = s.sta_index
                         WHERE bin. tkt_assignedTo = '$memberIDSearch'
						    OR bin.tkt_assignedTo  = '$memberGroupSearch'
						   AND bin.tkt_status <> 1						   
						   Group by bin.tkt_priority");	

[m_hutley]

I’d say it would be far easier to dynamically create the query than to try and shoehorn a pivot-table like ability into the existing engine. Pull a list of statuses from a status table, and use PHP to generate the query from the result.

[igor_g]

Pivot-table could be static and dynamic. In your case you would have exactly dynamic. There is no ability in SQL (probably except of MS Access version) to create dynamic pivot-table directly. So I agree with above comment of @m_hutley.

[r937]

completely offtopic, but did you want your WHERE clause to mean this –

 WHERE bin. tkt_assignedTo = '$memberIDSearch'
    OR (
       bin.tkt_assignedTo  = '$memberGroupSearch'
   AND bin.tkt_status <> 1			
       )

or this –

 WHERE (
       bin. tkt_assignedTo = '$memberIDSearch'
    OR bin.tkt_assignedTo  = '$memberGroupSearch'
       )
   AND bin.tkt_status <> 1			

because the way you have it, it’s being evaluated as the former