Getting sum() on a different distinct row MySQL

Question

Hello,

I was looking on different questions on this issue, but couldn't find an answer for my problem.

This is my query:

SELECT SUM( lead_value ) AS lead_value_sum, count( DISTINCT phone ) AS SUM, referer
FROM leads t1
INNER JOIN leads_people_details t2 ON t1.lead_id = t2.lead_id
INNER JOIN user_to_leads t3 ON t1.lead_id = t3.lead_id
WHERE lead_date
BETWEEN 20100716000000
AND 20100716235959
AND t1.site_id =8
GROUP BY t1.referer

I am trying to sum up the lead_value only of unique phone numbers. The count (Distinct phone) actually works and gives me the number of unique phones for each referer, but I can't seem to understand how should I SUM the lead_value for unique phone numbers at each referer.

Would appreciate any help you can give me, Eden

Edit: Table Structures

CREATE TABLE user_to_leads
 (
user_idINT(10) NOT NULL,
lead_idINT(10) NOT NULL,
site_idINT(10) NOT NULL,
lead_value INT(10) NOT NULL
 )

CREATE TABLE leads
 (
lead_id INT(100) NOT NULL auto_increment ,
site_id INT(10) NOT NULL ,
lead_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
vaild_date TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
referer VARCHAR(255) NOT NULL,
KEYWORD VARCHAR(255) NOT NULL,
upsaleINT(11) NOT NULL DEFAULT '0' ,
vaild INT(2) NOT NULL,
PRIMARY KEY (lead_id),
KEY lead_date (lead_date)
 )


CREATE TABLE leads_people_details
 (
lead_id INT(100) NOT NULL auto_increment ,
fullnameVARCHAR(255) NOT NULL,
phone VARCHAR(12) NOT NULL ,
email VARCHAR(255) NOT NULL,
homeVARCHAR(255) NOT NULL,
browser VARCHAR(255) NOT NULL,
browser_version VARCHAR(100) NOT NULL,
resolutionVARCHAR(255) NOT NULL,
IPVARCHAR(255) NOT NULL,
statusVARCHAR(255) NOT NULL DEFAULT '0',
COMMENT text NOT NULL,
PRIMARY KEY (lead_id)
 )

Answer 1

You say

For a particular referer,phone, the lead_value will always be the same

Based on the limited information you have given I think this should return the right answer. If you update your question with the requested information it will probably be possible to improve upon it though.

SELECT SUM(lead_value ) AS lead_value_sum, count(phone ) AS phone_count, referer
FROM
(
SELECT DISTINCT lead_value, phone, referer
FROM leads t1
INNER JOIN leads_people_details t2 ON t1.lead_id = t2.lead_id
INNER JOIN user_to_leads t3 ON t1.lead_id = t3.lead_id
WHERE lead_date
BETWEEN 20100716000000
AND 20100716235959
AND t1.site_id =8
) derived
GROUP BY referer

Upated after table structure posted

I don't really understand why have both leads_people_details and leads got a primary key and auto_increment column of lead_id that you are joining on? That would imply a 1-1 relationship between leads and leads_people_details? If so one of them probably shouldn't be an auto_increment to avoid the possibility of the ids getting out of synch without you realising.

Also there is no Primary Key on the user_to_leads table. Should there one on user_id, lead_id, site_id? Additionally you are not currently filtering by siteid on that table. Is that intentional? If not if you do that does that stop the duplicate records from coming back? If it doesn't then can you describe the significance of user_id in that table? You earlier said that For a particular referer,phone, the lead_value will always be the same can it differ by user_id? If so which should be used? If not why is user_id in that table?

A provisional query that might be closer is here but there are still the unresolved queries above.

SELECT SUM(lead_value ) AS lead_value_sum, count(phone ) AS phone_count, referer
FROM leads t1
INNER JOIN leads_people_details t2 ON t1.lead_id = t2.lead_id
INNER JOIN user_to_leads t3 ON t1.lead_id = t3.lead_id  
               and t1.site_id = t3.site_id
WHERE lead_date
BETWEEN 20100716000000
AND 20100716235959
AND t1.site_id =8