2. PHPRunner General questions
  3. [SOLVED]  SQL group by
This topic is locked
[SOLVED]

 SQL group by

 		1/3/2012 9:52:50 PM
PHPRunner General questions
author

Hi
I do not get it. I have the below SQL statement. I would expect that commission est etc (all fields with values) would be grouped and totaled. i.e.if commission had three lines with £10 on each a group result of £30 would show. But it does not it shows £10. Whats wrong with my code. (hay.. maybe I am just tired)
SELECT

users.id,

users.first_name,

users.second_name,

accounts.commission_est AS commission est (paid),

accounts.commision_actual AS commission actual (paid),

accounts.diff_commission,

accounts.commission_est_cal AS commission est (earned),

accounts.commission_actual_cal AS commission actual (earned),

accounts.balance_deposit_est,

accounts.balance_deposit_actual,

accounts.period,

accounts.sales_id

FROM accounts

LEFT OUTER JOIN users ON accounts.sales_id = users.id

GROUP BY accounts.period, accounts.sales_id
Regards
Richard
Sergey Kornilov admin 1/3/2012

Select list of GROUP BY query may only include group fields or aggregate functions like SUM. AVG, COUNT etc.

More info: http://www.webcheatsheet.com/sql/interactive_sql_tutorial/sql_groupby.php
C
 		cgphp 1/4/2012
SELECT

users.id,

users.first_name,

users.second_name,

SUM(accounts.commission_est) AS `commission est (paid)`,

SUM(accounts.commision_actual) AS `commission actual (paid)`,

SUM(accounts.diff_commission),

SUM(accounts.commission_est_cal) AS `commission est (earned)`,

SUM(accounts.commission_actual_cal) AS `commission actual (earned)`,

SUM(accounts.balance_deposit_est),

SUM(accounts.balance_deposit_actual),

accounts.period,

accounts.sales_id

FROM accounts

LEFT OUTER JOIN users ON accounts.sales_id = users.id

GROUP BY accounts.period, accounts.sales_id