Need mysql query help
I have this
$sql2 = "SELECT ImportedPaidLeads.SubmitDate, count(*) as groupcount from ImportedPaidLeads, temp_table WHERE ImportedPaidLeads.ssn = temp_table.ssn AND ImportedPaidLeads.SubmitDate BETWEEN '".$_POST['export_date']."' AND '".$_POST['export_date2']."' group by SubmitDate"; And it is working but it is not selecting DISTINCT records, menaing it is showing duplicates. I have tried the DISTINCT in there but then it only shows one record per group, need tomse gurus to help me make this select only distnct records. Z |
Can you post an example of what you get - it should only produce distinct records but if you have a one to many relationship then the one will be returned in an array for each of the many.
|
Getting wrong count
OK well results look like this:
For the period 2005-3-20 to 2005-3-26 There have been 167 apps There have been 75 leads Submit Date Leads 2005-03-20 4 2005-03-21 15 2005-03-22 22 2005-03-23 27 2005-03-24 17 2005-03-25 10 2005-03-26 3 Basically the counts are wrong, due to dup records on the one side of the join, tha actual distinct matching count is obtained with this: Code:
Z |
I'm still not sure what you are trying to get as a result. Do you not want the number of leads associated with a specific date? What count would you like beside each date?
|
Thats IT!
Yes, i want the CORRECT count for each date, the DISTINCT count for each date as shown above the Count for each date is not Distinct, rather it is including duplicates in the count for each date.
Thats why the actual no dup count is 75 and the sum of the date group counts is higher, i just found this: http://bugs.mysql.com/bug.php?id=8821 But dunno if it applies. Z this is what i am using to count the groups: Code:
|
Is there any way you could make the tabe data available.
Is that the version of MySQL you are using? |
Sure thing
MySQL version 3.23.58
Table ImportedPaidLeads looks like: Lender | SubmitDate | ssn LenderOne 2005-03-13 550-55-5555 LenderOne 2005-03-13 550-55-5005 LenderOne 2005-03-15 550-55-5215 LenderOne 2005-03-15 550-55-5134 ---Dups LenderOne 2005-03-15 550-55-5134 ---Dups LenderOne 2005-03-17 550-55-5005 LenderOne 2005-03-17 550-55-5215 ---Dups Table temp_table looks like ssn 550-55-5555 550-55-5005 550-55-5215 550-55-5214 550-55-5212 550-55-5211 Should be no dups in table_temp as i use distinct to grab it from one dbase and put it into my temp_table see full code below: Code:
Code:
Also, this seems slow, and sometime i get doubled results meaning, it will count everything twice. Thx for the help i owe ya Z |
Anyone else?
It seems this may be a bug in mysql, or possibly my version. I have tried 3-4 diff ways to do this and have had now luck!
Anyone else wanna help out? Z |
All times are GMT -5. The time now is 09:04 AM. |