Combining for a report

[Kat21]

I am trying to create a combination report that include both site where customers place orders and the sums of each column after. I am having trouble with the join operation. I am getting an error that says customer is not part of an aggregate function. Any help?

 

[SIG357]

Let's see the SQL statement

 

[Kat21]

SELECT DISTINCTROW Customer.CustType, Sum([Combined Sales By Customer].UnitsMTD) AS [Sum Of UnitsMTD], Sum([Combined Sales By Customer].SalesMTD) AS [Sum Of SalesMTD], Sum([Combined Sales By Customer].CostMTD) AS [Sum Of CostMTD], Sum([Combined Sales By Customer].SalesPYTD) AS [Sum Of SalesPYTD], Sum([Combined Sales By Customer].CostPYTD) AS [Sum Of CostPYTD], Sum([Combined Sales By Customer].SalesYTD) AS [Sum Of SalesYTD], Sum([Combined Sales By Customer].CostYTD) AS [Sum Of CostYTD]
FROM Customer, [Combined Sales By Customer]
GROUP BY Customer.CustType;

 

[SIG357]

It looks like you need a join between the Customer table and the [Combined Sales By Customer] table. Since there's no join or a WHERE condition (like WHERE Customer.CustType = [Combined Sales By Customer] .CustType), this SQL statement is trying to make sense a pure cross product of the two tables, which it can't do.

 

[tlbroadbent]

The message doesn't make sense given that there is no column named "Customer" in the selection list. However, SIG357 is correct about needing a JOIN criteria. I also recommend using aliases to cut down the clutter and increase readability of the query. I also find queries easier to read and work with if I avoid spaces in names. Instead of [Sum Of UnitsMTD], I would use SumUnitsMTD or UnitsMTDSum.

SELECT
a.CustType,
Sum(b.UnitsMTD) AS [Sum Of UnitsMTD],
Sum(b.SalesMTD) AS [Sum Of SalesMTD],
Sum(b.CostMTD) AS [Sum Of CostMTD],
Sum(b.SalesPYTD) AS [Sum Of SalesPYTD],
Sum(b.CostPYTD) AS [Sum Of CostPYTD],
Sum(b.SalesYTD) AS [Sum Of SalesYTD],
Sum(b.CostYTD) AS [Sum Of CostYTD]
FROM Customer a Inner Join [Combined Sales By Customer] b
ON a.CustType=b.CustType
GROUP BY a.CustType;
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Kat21]

I use that and it still tells me that site is not part of an aggregate function.
SELECT DISTINCTROW Customer.CustType, Sum([Combined Sales By Customer].UnitsMTD) AS [Sum Of UnitsMTD], Sum([Combined Sales By Customer].SalesMTD) AS [Sum Of SalesMTD], Sum([Combined Sales By Customer].CostMTD) AS [Sum Of CostMTD], Sum([Combined Sales By Customer].SalesPYTD) AS [Sum Of SalesPYTD], Sum([Combined Sales By Customer].CostPYTD) AS [Sum Of CostPYTD], Sum([Combined Sales By Customer].SalesYTD) AS [Sum Of SalesYTD], Sum([Combined Sales By Customer].CostYTD) AS [Sum Of CostYTD]
FROM Customer, [Combined Sales By Customer]
WHERE Customer.CustType = [Combined Sales ByCustomer].CustType
GROUP BY Customer.CustType;

 

[tlbroadbent]

Is [Combined Sales By Customer] a View? If so, can you select from that View?

SELECT * FROM [Combined Sales By Customer]

Which RDMS are you using? Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Kat21]

Thanks Terry but I am still getting a error that says site is not part of an aggregate function and it doesnt make sense. I am trying to work off another query called Combined Sales By customer is this the reason I am having the problem

 

[tlbroadbent]

Are you using Access? Does the other query, [Combined Sales By Customer], work by itself? Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Kat21]

Yes I am using access and I just found out the other query was working but now it is giving me the same error.

 

[tlbroadbent]

I'm sure as you correct the [Combined Sales By Customer] query, the other will work. Just verify that an aggreagate function is set for all columns in the first query - GROUP BY, SUM, COUNT, etc.

BTW: you'd get much faster and specific help in an Access forum instead of the ANSI SQL forum. Access is not ANSI compliant! Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin