Q:

SQL Formatting - Assistance Required

Good Morning,
Apologies in advance but I am still relatively new to SQL.
I am currently getting this error when I run the script below and am not sure why. It is specified in the where and group by clause. Can you give me some pointers as to where I am going wrong?
8120, Level 16, State 1, Line 4
Column 'contact_legacies.contact_number' is invalid in the select list because it is not        contained in either an aggregate function or the GROUP BY clause.
SELECT
cl.contact_number as contact_number,
cl.legacy_number as legacy_number,
c.label_name as label_name,
cl.source_date as source_date,
cl.legacy_status as legacy_status,
lb.expected_value as total_expected,
sum(lbr.amount) as total_received,
sum(lb.expected_value)- sum(lbr.amount) as amount_outstanding, 
cl.agency_notification_date as date_notified_by_agency,
cl.death_notification_date as executor_notification_date,
cl.date_of_death as date_of_death
--lb.bequest_status as bequest_status,
--lb.bequest_type as bequest_type,
--lb.bequest_sub_type as bequest_sub_type,
--a.address_line1 as address_line1,
--a.address_line2 as address_line2,
--a.town as town,
--a.county as county,
--a.postcode as postcode,
--a.country as country

FROM
contact_legacies cl

INNER JOIN contacts c ON cl.contact_number = c.contact_number
INNER JOIN legacy_bequests lb ON cl.legacy_number = lb.legacy_number
INNER JOIN addresses a on a.address_number = c.address_number
INNER JOIN legacy_bequest_receipts lbr on cl.legacy_number = lbr.legacy_number

WHERE
cl.legacy_status = 'IP' --AND
--cl.source_date between %1 AND %2

ORDER BY
cl.contact_number,cl.legacy_number,c.label_name,cl.source_date,cl.legacy_status,lb.expected_value,sum(lbr.amount),
sum(lb.expected_value)- sum(lbr.amount),cl.agency_notification_date,cl.death_notification_date,cl.date_of_death

I also want to add a WHERE clause which only takes into account the amount_outstanding from the SELECT statement where the value is greater than zero. How do I do this?
Thanks in advance.

A:

In your query there is ORDER BY not GROUP BY clause :).
About your WHERE question syou should use HAVING clause
HAVING sum(lb.expected_value)- sum(lbr.amount) > 0 

So your query should look like
SELECT
cl.contact_number as contact_number,
cl.legacy_number as legacy_number,
c.label_name as label_name,
cl.source_date as source_date,
cl.legacy_status as legacy_status,
lb.expected_value as total_expected,
sum(lbr.amount) as total_received,
sum(lb.expected_value)- sum(lbr.amount) as amount_outstanding, 
cl.agency_notification_date as date_notified_by_agency,
cl.death_notification_date as executor_notification_date,
cl.date_of_death as date_of_death
--lb.bequest_status as bequest_status,
--lb.bequest_type as bequest_type,
--lb.bequest_sub_type as bequest_sub_type,
--a.address_line1 as address_line1,
--a.address_line2 as address_line2,
--a.town as town,
--a.county as county,
--a.postcode as postcode,
--a.country as country

FROM
contact_legacies cl

INNER JOIN contacts c ON cl.contact_number = c.contact_number
INNER JOIN legacy_bequests lb ON cl.legacy_number = lb.legacy_number
INNER JOIN addresses a on a.address_number = c.address_number
INNER JOIN legacy_bequest_receipts lbr on cl.legacy_number = lbr.legacy_number

WHERE
cl.legacy_status = 'IP'

GROUP BY
cl.contact_number,cl.legacy_number,c.label_name,cl.source_date,cl.legacy_status,lb.expected_value,cl.agency_notification_date,cl.death_notification_date,cl.date_of_death

HAVING sum(lb.expected_value)- sum(lbr.amount) > 0 

ORDER BY
cl.contact_number,cl.legacy_number,c.label_name,cl.source_date,cl.legacy_status,lb.expected_value,sum(lbr.amount),
sum(lb.expected_value)- sum(lbr.amount),cl.agency_notification_date,cl.death_notification_date,cl.date_of_death

In GROUP BY clause you need to add all fields which are not in aggregates funcions (sum, count, max,min etc.)
You can read more about aggregates here: http://www.w3schools.com/sql/sql_functions.asp
And about HAVING clause you can read here: http://www.w3schools.com/sql/sql_having.asp

