I am querying a table for all the available records. The results have a primary key called ticket_id
<cfquery name="get_all_active_tickets">
SELECT *
FROM service_ticket
where technician_id != <CFQUERYPARAM Value="#techID#"> AND technician_name != ""
</cfquery>
The query returns 42 unique records, each record has a unique value for the column ticket_id.
I then want to query another another table for all records that also contain the primary key ticket_id and SUM a column in those results called service_qty for those related records. My goal is to add up all the values of service_qty for records that contains the same ticket_id value, and compare those values.
I am trying to do it like this:
<cfset IDs = valueList(get_all_active_tickets.ticket_id) >
<cfquery name="high_hours_tickets">
select SUM(service_qty) as total, ticket_id
from service_ticket_detail
where ticket_id in (<cfqueryparam cfsqltype="cf_sql_integer" value="#IDs#" list="true">)
</cfquery>
However the results are not as expected. The record count for the query "high_hours_tickets" is only 1 and it looks like it is totaling the column 'service_qty' for all the records that are returned, not just the records that have the same ticket_id value.
How can I fix this? Thank you.
A solution that works, thanks to Ageax's contributions below:
<cfset IDs = valueList(get_all_active_tickets.ticket_id) >
<cfquery name="high_hours_tickets" datasource="#datasource#">
SELECT t.ticket_id, SUM( d.service_qty) AS totalQty
FROM service_ticket t
INNER JOIN service_ticket_detail d ON d.ticket_id = t.ticket_id
WHERE t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer">
AND t.technician_name <> ""
GROUP BY t.ticket_id
HAVING SUM( d.service_qty) > 2
</cfquery>
<cfoutput><cfset lstIDs = ValueList(high_hours_tickets.ticket_id) /></cfoutput>
<cfquery name="flagged_tickets" datasource="#datasource#">
select *
from service_ticket
where ticket_id IN (
<cfqueryparam
value="#lstIDs#"
cfsqltype="CF_SQL_INTEGER"
list="yes"
/>
)
</cfquery>
Update:
Since there was some confusion about the table structure and desired results, I put together a SQL Fiddle based on your description. It's a great tool for sharing schemas and sample data, and is helpful in avoiding a lot of back forth with query issues :-) I don't know which version of MySQL you're using, so I'll assume an older version for best compatibility.
If I'm understanding correctly, the goal is to display all service_ticket records, having an overall service_qty > 2. To do that, use a subquery to SUM the service_qty values, by ticket_id, and drop any that are <= 2. Then join those results back to the main ticket table to get the details (name, address, etc...).
Query:
SELECT t.ticket_id
, t.address
, t.name
-- ... additional columns
, total.qty
FROM service_ticket t INNER JOIN
(
SELECT ticket_id
, SUM( service_qty) AS Qty
FROM service_ticket_detail
GROUP BY ticket_id
HAVING Qty > 2
)
total ON total.ticket_id = t.ticket_id
WHERE t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer">
AND t.technician_name <> ''
Original:
Forget about the separate queries. This is a job for a JOIN. They're designed for just this purpose.
SELECT t.ticket_id, SUM( d.service_qty) AS totalQty
FROM service_ticket t
INNER JOIN service_ticket_detail d ON d.ticket_id = t.ticket_id
WHERE t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer">
AND t.technician_name <> ''
GROUP BY t.ticket_id
HAVING SUM( d.service_qty) > 2
IMO, JOIN's are indispensable. If you're unfamiliar with them, I'd strongly recommend reading a few introductory tutorials on JOIN's. You'll be glad you did.