By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

group by distinct

P: 4
hey guys i have a problem with dups in my statement. ive tried using multiple select statements but i just cant get it. any help would be greatly appreciated.

here is the sql:

SqlJunk = " SELECT distinct count(*) as icount, Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, Quiz.ea, Quiz.fee, Quiz.FirstName, " & _
" Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, Quiz.tax_consultant, " & _
" Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID , " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, " & _
" Marks.packet, Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, Marks.levy, " & _
" Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, Marks.closed, marks.vote , sum(Billing.Amount) as sumamount , billing.status " & _
" FROM Quiz LEFT JOIN (Billing RIGHT JOIN Marks ON Billing.qID = Marks.qID) ON Quiz.qID = Marks.qID " & _
" " & _
" GROUP BY Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, " & _
" Quiz.ea, Quiz.fee, Quiz.lead, Quiz.FirstName, Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, " & _
" Quiz.tax_consultant, Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID, " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, Marks.packet, " & _
" Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, " & _
" Marks.levy, Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, " & _
" Marks.closed, marks.vote, billing.status "

the results bring back multiples because status can be equal to 1 or 2 but i just want distinct on the group by.

table structure:

table quiz
client information

table marks
marking services complete or not

table billing
has the multiples in it - has a qid for each payment that is entered - this is where my problem occurs.

thanks again
Jan 9 '09 #1
Share this Question
Share on Google+
4 Replies


P: 4
i researched this problem and tried fixing it using some selects. i am still getting an error. thx for the help

SELECT * FROM quiz q LEFT JOIN marks m ON m.qid=q.qid LEFT JOIN (SELECT b1.* FROM billing b1 INNER JOIN (SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid) b2 ON b2.qId=b1.qId AND b2.status2=b1.qId)b ON b.qId=e.qId



error:
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'm.qid=q.qid LEFT JOIN (SELECT b1.* FROM billing b1 INNER JOIN (SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid) b2 ON b2.qId=b1.qId'.

/Engine2.asp, line 591
Jan 11 '09 #2

100+
P: 365
im not an expert with sql i just use the builder for mine but this line:

(SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid)
looks to me like you shouldt have it wrapped in brackets?

just a guess really

Dan
Jan 11 '09 #3

P: 4
yeah im using mutiple selects to join and remove duplicates - i dont think the brackets are the problem with the code - but i appriciate the response man. also i corrected an error heres the updated code:

SELECT * FROM quiz q LEFT JOIN marks m ON m.qid=q.qid LEFT JOIN (SELECT b1.* FROM billing b1 INNER JOIN (SELECT qid,MAX(status) AS status2 FROM billing GROUP BY qid)b2 ON b2.qid=b1.qid AND b2.status2=b1.qid)b ON b.qid=q.qid

i could be getting nulls or somehting that would throw the code off
Jan 11 '09 #4

P: 4
i just cheated and used min,max, and avg to remove the duplicates but id still like to know how to fix the above code. here is the working code:


" SELECT count(*) as icount, Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, Quiz.ea, Quiz.fee, Quiz.FirstName, " & _
" Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, Quiz.tax_consultant, " & _
" Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID , " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, " & _
" Marks.packet, Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, Marks.levy, " & _
" Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, Marks.closed, marks.vote , sum(Billing.Amount) as sumamount , max(billing.status) as status , min(billing.status) as mstatus , avg(billing.status) as astatus " & _
" FROM Quiz inner JOIN (Billing inner JOIN Marks ON Billing.qID = Marks.qID) ON Quiz.qID = Marks.qID " & _
" " & _
" GROUP BY Quiz.LessonID, Quiz.Number, quiz.registerdate, Quiz.Market, Quiz.Division, Quiz.LastName, Quiz.zip, " & _
" Quiz.ea, Quiz.fee, Quiz.lead, Quiz.FirstName, Quiz.phone, Quiz.phone_work, Quiz.phone_cell, Quiz.phone_fax, Quiz.ssn, Quiz.ssn_her, Quiz.email, Quiz.city, Quiz.state, " & _
" Quiz.tax_consultant, Quiz.business_name, Quiz.business_address, Quiz.business_city, Quiz.business_zip, Quiz.business_state, Quiz.qID, Marks.userID, Marks.lessonID, Marks.mark, Marks.qID, " & _
" Marks.ert, Marks.lien, Marks.alarm1, Marks.a1_comments, Marks.a1_onoff, Marks.alarm2, Marks.a2_comments, Marks.a2_onoff, Marks.alam3, Marks.a3_comments, Marks.a3_onoff, Marks.packet, " & _
" Marks.poa, Marks.engagement, Marks.taxreturns, Marks.oicsubmitted, Marks.oiccomplete, Marks.installment, Marks.hardship, " & _
" Marks.levy, Marks.ins_health, Marks.ins_life, Marks.payroll_svc, Marks.cancelled, " & _
" Marks.closed, marks.vote "
Jan 11 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.