Solved Count Number of times a customer appears (1 Viewer)

[Number11]

I need a query to count the number of times a customer account number appears in a table and update a count total with the results, but to only count the records that have the OrderStatus as "Completed"


SELECT [CustomerAC], Sum(IIf([OrderStatusDevice] = "Completed", 1, 0)) As CountTotal
FROM Orders_Master


This is not working and i am getting...

Your query does not include the specified expression "CustomerAC" as part of an aggregate funtion.

 

[Ranman256]

select [customer], Count([customer]) from table where [OrderStatus]='Completed'

 

[arnelgp]

you can also use Total query?

select [CustomerAC], Sum([OrderStatusDevice]) As CountTotal
FROM Order_Master
Where [OrderStatusDevice] = "Completed"
Group By [CustomerAC]

 

[plog]

Neither of those queries worked did they? Ranman did the exact same thing you did and arnel tried to SUM text.

Back to your query:

SELECT [CustomerAC], Sum(IIf([OrderStatusDevice] = "Completed", 1, 0)) As CountTotal
FROM Orders_Master

Whenever you use an aggregate function (SUM, COUNT, MAX, etc.) in the SELECT, every other field in the SELECT that does not have an aggregate function must appear in the GROUP BY clause. You have no GROUP BY. So, you need to add one and put [CustomerAC] in it.