Long time listener, first time caller. Hope I don't screw up my first question! I am working on a report generation application, and I've just run into my first situation where I need a fairly complicated query.
Table 1 is called 'performance' and contains the columns 'merchant_id', 'publisher', 'sales', 'orders', and 'report_date'.
Table 2 is called 'publishers' and contains the columns 'publisher_id', 'publisher', 'status', 'location', and a few others.
I need to put in my report the top ten performers ordered by sales. I need their name (publisher), their ID (publisher_id), their sales total (sales), orders total (orders) and all those things within a specified time period (report_date) and for a specific merchant (merchant_id).
I am completely new to JOINs. This is what I have tried so far:
$sql = "SELECT
performance.publisher,
publishers.publisher_id,
SUM(performance.orders) AS orders,
SUM(performance.sales) AS sales
FROM performance
INNER JOIN publishers
ON performance.publisher=publishers.publisher
WHERE performance.merchant_id='$merchant->ID'
AND (performance.report_date BETWEEN '{$dateStart}' AND '{$dateEnd}')
GROUP BY publishers.publisher_id
ORDER BY sales DESC LIMIT 10";
I then run the query and create an array of the results.
And what is returned is indeed ten results! The publisher and publisher_id are returned correctly. It's the sums that are messed up. I've tried messing with the start and end dates, to no avail. I just can't see where the returned numbers are coming from.
How should I structure the query?
Hopefully this question isn't too specific or narrow - if so I'm happy to try to rewrite it to be more valuable to a broader audience.
EDIT:
A vital piece of information was left out of my original summation. The publishers table also has a column called 'merchant_id' and each publisher will be entered into this table on a per-merchant_id basis. For example, publisher John Doe, publisher_id 123, merchant_id 12345 AND publisher John Doe, publisher_id 123, merchant_id 56789.
When I did the join, I expected to get results only related to a specific merchant_id, as you can see by the WHERE clause. But that WHERE clause only took the merchant_id of the performance table into account. It needs to take the merchant_id in the publisher's table into account as well. Now I just need to figure out the query structure for that.
I'm new to StackOverflow, so thank you all for being patient and offering questions and advice. I don't know if editing like this is the proper thing to do or if I'm supposed to create an Answer to the question or what.
Three things:
SUM() and COUNT(). It seems like you may be using SUM() on your orders where COUNT() is what you need.GROUP BY. Read this: http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html Try GROUP BY performance.publisher, publishers.publisher_id in place of what you have.ON condition will show up in the result. This can cause double- or multiple- counting in your results. It's not clear whether you are double-counting. But be aware of it.