JDatabase query with SQL JOIN and SUM

[Matt Kane]

I'm trying to use reproduce the following query using JDatabase:

SELECT a.ID, a.FirstName, a.LastName, sum(b.DonationAmount) AS Amount
FROM `band_donorRegistration` AS a
INNER JOIN band_donations AS b
ON a.ID = b.DonorID
GROUP BY b.DonorID
ORDER BY a.ID

My implementation looks like this but no matter how I try the SUM(), Joomla tells me the field doesn't exist in the database:

$query
->select($db->qn(array('a.ID', 'a.FirstName', 'a.LastName')))
->select($db->qn(array('SUM(b.DonationAmount)', 'Amount')))
->from($db->qn('band_donorRegistration', 'a'))
->join('INNER', $db->qn('band_donations', 'b') . ' ON ' . $db->qn('a.ID') . ' = ' . $db->qn('b.DonorID'))
->group($db->qn('b.DonorID'));

Can anyone help me spot what I'm doing wrong?

Thanks.

[James Garrett]

I think you may kick yourself when you see it.

Look at this: $db->qn(array('SUM(b.DonationAmount)'

The QuoteName method puts quotes around what ever you give it - it isn't clever - it'll totally prevent that SQL SUM function from working just like you told it to.

Also, quoting is for dealing with user input, whitespace, special characters, and db engine differences.  If you're only implementing this on regular MySQL then you don't need to quote anything in your select statement.

You can totally write:

->select('a.ID, a.FirstName, a.LastName, SUM(b.DonationAmount) AS Amount')

and it is fine.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-general+unsub...@googlegroups.com.
To post to this group, send an email to joomla-dev-general@googlegroups.com.
Visit this group at https://groups.google.com/group/joomla-dev-general.
For more options, visit https://groups.google.com/d/optout.

--

Regards,
James.

Preflight Web-development.

ja...@preflight.com.au