Criteria and aggregation in a query

[Elina]

Hi all,

I am trying to use criteria for Product column (i.e keep only product A) Total for Sales column to aggregate the values and it seems that is cannot be done simultaneously.


Product Sales
A 34
A 45
B 21
A 11
B 23

Eventually i want to get a table like below

Product Sales
A 90

I get the following warning : you tried to execute a query that does not include the specifies expression as part of an aggregate function.

Why is this happening. I don't want to create 2 different queries to achieve it. Is there a way to do it i the same query?

Thanks in advance

 

[CJ_London]

Please post your sql for your query, then we can help

 

[Elina]

Please find the sql syntax below


SELECT [Table1].[Product], Sum([Table1].[Sales]) AS [SumOfSales]
FROM [Table1]
GROUP BY [Table1].[ Product]
HAVING ((([Table1].[ Product])=" "));

 

[CJ_London]

Try this instead

SELECT [Table1].[Product], Sum([Table1].[Sales]) AS [SumOfSales]
FROM [Table1]
WHERE ((([Table1].[ Product])=" "))
GROUP BY [Table1].[ Product];

I presume this is a sample, the query will return any row where product is a single space

 

[Elina]

SELECT [Table1].[Product], Sum([Table1].[Sales]) AS [SumOfSales]
FROM [Table1]
GROUP BY [Table1].[ Product]
HAVING ((([Table1].[ Product])="A"));

Sorry it should be "A" and not single space. Do I still use the same syntax with A in the place of sinle space?

 

[CJ_London]

Do I still use the same syntax with A in the place of sinle space?

yes - assuming product is text and not numeric

 

[Elina]

It doesn't seem to work. It returns product A but sales column is empty

 

[Elina]

Actually it's working. I forgot to add something.

Thanks a lot for your help.

 

[CJ_London]

I suspect you are providing a generic code - the SQL you originally provided works OK as provided with a table as described, but the error you generated is due to using one of the group by fields elsewhere in the query - which is solved by using the WHERE statement rather than HAVING.

Please review what I sent you against what you have actually used

If you are not getting a value for sales, there will be another reason - perhaps there are no values?