Column X is invalid in the select list ...not contained in..

I'm having a problem with a select query. I want to return a GrandTotal
value grouped by name, but the query analyzer throws an error:

Column 'ct.Total' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.

If I add that column to the group by clause, the results won't group
properly. Is there a workaround or is this just a bad query?

/*
Mock Query
*/
SELECT c.Name, ISNULL(ct.Total, 0) + SUM(ISNULL(divt.Total, 0)) AS
GrandTotal
FROM @Customer c
LEFT JOIN @Invoice ct ON c.Customer = ct.Customer
LEFT JOIN @Invoice divt ON c.Customer = divt.ParentCustomer
GROUP BY c.Name
ORDER BY c.Name


/*
Sample mock/data
*/

DECLARE @Customer Table (
Customer int,
Name varchar(50),
ParentCustomer int
)

DECLARE @Invoice Table (
Invoice int,
Customer int,
ParentCustomer int,
Total money
)

INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (1, 'Customer
A', 0)
INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (2, 'Customer
A: Division 1', 1)
INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (3, 'Customer
A: Division 2', 1)
INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (4, 'Customer
B', 0)
INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (5, 'Customer
C', 0)
INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (6, 'Customer
C: Division 1', 5)

INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (1,
1, 0, 50.00)
INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (2,
1, 0, 50.00)
INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (3,
2, 1, 25.00)
INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (4,
2, 1, 30.50)
INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (5,
3, 1, 45.00)
INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (6,
4, 0, 75.00)
INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (7,
6, 5, 50.00)

Thanks for providing the DDL and sample data. The only thing missing was
the result you wanted from your sample data. I'm guessing that the result
you want is:

Name Total
GrandTotal
-------------------------------------------------- --------------------- ---------------------
Customer A 100.0000
200.5000
Customer A: Division 1 55.5000
55.5000
Customer A: Division 2 45.0000
45.0000
Customer B 75.0000
75.0000
Customer C .0000
50.0000
Customer C: Division 1 50.0000
50.0000

If so, then use the following:

SELECT x.Name,
SUM(CASE WHEN x.Source = 1 THEN Coalesce(x.Total,0) ELSE 0 END) As Total,
SUM(Coalesce(x.Total,0)) AS GrandTotal
FROM (SELECT c.Name, ct.Total, 1 As Source
FROM @Customer c
LEFT JOIN @Invoice ct ON c.Customer = ct.Customer
UNION ALL
SELECT c.Name, divt.Total, 2 As Source
FROM @Customer c
LEFT JOIN @Invoice divt ON c.Customer = divt.ParentCustomer) AS x
GROUP BY x.Name
ORDER BY x.Name;

Tom

"Janet" wrote in message

> I'm having a problem with a select query. I want to return a GrandTotal
> value grouped by name, but the query analyzer throws an error:
>
> Column 'ct.Total' is invalid in the select list because it is
> not contained in either an aggregate function or the GROUP BY
> clause.
>
> If I add that column to the group by clause, the results won't group
> properly. Is there a workaround or is this just a bad query?
>
> /*
> Mock Query
> */
> SELECT c.Name, ISNULL(ct.Total, 0) + SUM(ISNULL(divt.Total, 0)) AS
> GrandTotal
> FROM @Customer c
> LEFT JOIN @Invoice ct ON c.Customer = ct.Customer
> LEFT JOIN @Invoice divt ON c.Customer = divt.ParentCustomer
> GROUP BY c.Name
> ORDER BY c.Name
>
>
> /*
> Sample mock/data
> */
>
> DECLARE @Customer Table (
> Customer int,
> Name varchar(50),
> ParentCustomer int
> )
>
> DECLARE @Invoice Table (
> Invoice int,
> Customer int,
> ParentCustomer int,
> Total money
> )
>
> INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (1,
> 'Customer
> A', 0)
> INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (2,
> 'Customer
> A: Division 1', 1)
> INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (3,
> 'Customer
> A: Division 2', 1)
> INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (4,
> 'Customer
> B', 0)
> INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (5,
> 'Customer
> C', 0)
> INSERT INTO @Customer (Customer, Name, ParentCustomer) VALUES (6,
> 'Customer
> C: Division 1', 5)
>
> INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (1,
> 1, 0, 50.00)
> INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (2,
> 1, 0, 50.00)
> INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (3,
> 2, 1, 25.00)
> INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (4,
> 2, 1, 30.50)
> INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (5,
> 3, 1, 45.00)
> INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (6,
> 4, 0, 75.00)
> INSERT INTO @Invoice (Invoice, Customer, ParentCustomer, Total) VALUES (7,
> 6, 5, 50.00)
>
>
>