dee19802000 Posted January 25, 2010 Share Posted January 25, 2010 Hey I am having some difficulty trying to get an inner join to sum a column SELECT quotes.quote_id, quotes.m_id, quotes.author, quotes.date_now, quotes.valid_date, quotes.comment, quotes.m_comment, quotes.reference, quotes_det.id_q, quotes_det.q_id, quotes_det.item_code, quotes_det.quantity, quotes_det.quote, quotes_det.active, SUM(quotes_det.quote) FROM (quotes LEFT JOIN quotes_det ON quotes_det.q_id=quotes.quote_id) WHERE quotes. m_id=%s GROUP BY quotes.reference i keep getting 0 as my returned result. What do i need o do to get the correct result? Damain Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 25, 2010 Share Posted January 25, 2010 Hi Not sure why you are getting 0, but you can't really use a SUM like that. You are grouping on a single column yet you are bringing back the details from multiple columns which are from different rows of the same table on which you are summing the column. MySQL will give you random row details (most flavours of SQL would just give you an error). All the best Keith Quote Link to comment Share on other sites More sharing options...
dee19802000 Posted January 25, 2010 Author Share Posted January 25, 2010 how would i them sums up the quotes based on the reference number? Dee Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 25, 2010 Share Posted January 25, 2010 Hi Realistically something like this (note, not sure if quotes.m_id will be constant for a reference, if so you can take out the where from the subselect):- SELECT quotes.quote_id, quotes.m_id, quotes.author, quotes.date_now, quotes.valid_date, quotes.comment, quotes.m_comment, quotes.reference, quotes_det.id_q, quotes_det.q_id, quotes_det.item_code, quotes_det.quantity, quotes_det.quote, quotes_det.active, quoteSum.SumOfQuotes FROM quotes LEFT JOIN quotes_det ON quotes_det.q_id=quotes.quote_id LEFT JOIN (SELECT z.reference, SUM(y.quote) AS SumOfQuotes FROM quotes z LEFT JOIN quotes_det y ON y.q_id = z.quote_id WHERE quotes.m_id=%s GROUP BY z.reference) quoteSum ON quotes.reference = quoteSum.reference WHERE quotes.m_id=%s Basically have a subselect to get all the sums of quotes per reference and then JOIN that with the results from the tables which get the rest of the fields. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.