3 replies to this topic

[ykcul]

I am having trouble getting this query to work.. I am trying to get multiple COUNT() and SUM() functions on joined tables and failing miserably.
Here is the SQL I am working(failing) with now but I want to add two more SUM functions. Can I do this? is the GROUP BY throwing me off? Do I need subqueries? When I add the "COUNT(conversions.`id`) as conversions" it gives me a wrong value.


SELECT clicks.`keyword_id`, COUNT(clicks.`keyword_id`) as count, keywords.keyword, offers.`name` as offer_name, offers.`payout`, COUNT(conversions.`id`) as conversions,
traffic_sources.name as traffic_source_name, traffic_sources.id as traffic_source_id
FROM clicks
JOIN keywords ON keywords.`id` = clicks.`keyword_id`
JOIN offers ON offers.`id` = keywords.`offer_id`
JOIN conversions ON keywords.`id` = conversions.`keyword_id`
JOIN traffic_sources ON traffic_sources.`id` = keywords.`traffic_source_id`
WHERE clicks.`updated_at` >= {$start_date}
AND clicks.`updated_at` < {$end_date}
GROUP BY clicks.`keyword_id`
ORDER BY count DESC


Currently I am querying the db while looping through the records to get my additional info but would like to streamline it.

I am generating a report here and would also like to show the number of clicks, conversions, along with the sum of revenue and spend based on the keyword.id. They are all in separate tables. So is it possible to make an uberquery to have it all???

[Orjan]

as you make a large query with many joins, you will get a table that contains everything of everything. this means that two counts would probably show the same result.

It's not always easy with joins, and you need to think if you want inner (standard), left or right joins to get the result you want. in normal joins, the non existing join fields get filled up and therefore gives wrong answers.

sums can easily be added with example: SUM(col1) + SUM (col2) AS totcol12

[ykcul]

Figured it out.. I needed nested selects to do multiple COUNT and SUM functions in the query in order to get all the info i wanted in a single row... here is the final query.

Hope it helps someone out.

SELECT clicks.`keyword_id`, COUNT(clicks.`keyword_id`) as count, keywords.keyword, offers.`name` as offer_name, offers.`payout`, traffic_sources.name as traffic_source_name, traffic_sources.id as traffic_source_id,
(SELECT COUNT(conversions.`id`) FROM conversions WHERE conversions.`keyword_id` = keywords.`id` AND conversions.`updated_at` >= '2008-11-11' AND conversions.`updated_at` < '2008-11-12' ) as conversions,
(SELECT SUM(conversions.`revenue`) FROM conversions WHERE conversions.`keyword_id` = keywords.`id` AND conversions.`updated_at` >= '2008-11-11' AND conversions.`updated_at` < '2008-11-12' ) as revenue,
(SELECT SUM(spendings.`spend`) FROM spendings WHERE spendings.`keyword_id` = keywords.`id` AND spendings.`date` >= '2008-11-11' AND spendings.`date` < '2008-11-12' ) as spend
FROM clicks
JOIN keywords ON keywords.`id` = clicks.`keyword_id`
JOIN offers ON offers.`id` = keywords.`offer_id`
JOIN traffic_sources ON traffic_sources.`id` = keywords.`traffic_source_id` WHERE clicks.`updated_at` >= '2008-11-11'
AND clicks.`updated_at` < '2008-11-12'
GROUP BY clicks.`keyword_id`
ORDER BY count DESC

[WingedPanther]

When constructing a query like this, one of the first things I do is .... work incrementally. First, try doing this WITHOUT the count and with all relevant data (referenced in where, etc). Also, I would make the joins explicit (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN) so that you can be sure you aren't getting odd data.

Also, it looks like you're missing fields in your group by. What DB type are you targeting (MySQL, SQL Server, Oracle, other)? What error messages/erroneous results are you getting?