concatenate fields plus grouping and counting results

[slimjen]

Hi all,


I have a table Called tblproduct
I am trying to create a query to first; concatenate fields then use the said field to group on in the same table...

Select City + '_' + State as Demograph, Sum(Product_Cost) as Total, SaleDate from tblProduct  Groupby Demograph having count (Demograph) >5

I need the result as follow: concatenate the City and State, Count the number of products for the same saledate five of more and give me the total Cost.
It gives me an error stating that I can't concatenate a field because its not an aggregate field? Can anyone give me some guidance?

Maybe I'm missing a step

Thanks Slim

[pbaldy]

Try

GROUP BY City + '_' + State

Edit, as a rule you should use & to concatenate, not +.

[slimjen]

Thanks for responding paul...I'm kinda of lost. I'm still getting the error. Should I be using a subquery being that I need to concatenate a field I want to use in the result. I'm not strong in subqueries though. It doesn't seem that I can group on and total on an alias that's not in the table. Can you assist please? I need to concatenate the fields for City and State as Demograph, get the sum of the product cost for the products with the saledate greater than 5..need to return results as Demograph, saledate, Sumof Product Cost and count the number of product per demograph. Hope I'm making sense.

[slimjen]

Is there anyone that can assist pls.

[Minty]

You cant refer to the Alias in the grouping section sometimes. Try something like;

Select City & '_' & State as Demograph, Sum(Product_Cost) as Total, SaleDate from tblProduct Group by City & '_' & State , SaleDate Having count (City & '_' & State) >5

[slimjen]

You cant refer to the Alias in the grouping section sometimes. Try something like;

Select City & '_' & State as Demograph, Sum(Product_Cost) as Total, SaleDate from tblProduct Group by City & '_' & State , SaleDate Having count (City & '_' & State) >5

Thanks for the response...It's throwing an error that the data types varchar and varchar are incompatiable in the '&' operator

[Minty]

Can you post up your database (zipped) with some sample data - enough to get a query result ?