I'm trying to get a count of orders for every account that fits the criteria in the 'where' clause as shown in the code below.
There is nothing in the Product view that I can 'AND' to the 'where' clause to match the account id's up to. When I run the query, I get the following error in SQL:
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Is there some way I can rewrite the code below to get the order count to parent to the correct account? the code works and gets the right total, I just need to get the 'count' generated back to the proper account somehow.
Thanks for your help!
SET new_ordercount = (
FROM dbo.SalesOrder AS so, dbo.Product AS p
(New_source_paid_amount <> 0)
AND (DATEPART(year, so.New_source_event_date) = DATEPART(year,
GETDATE())) AND (so.new_source_event_id = p.new_source_event_id) AND (p. new_include_in_rollup = '1')and(so.New_source_order_type not like 'S') and ((so.new_createdby LIKE 'W%') or (so.new_createdby LIKE 'P%'))
GROUP BY AccountId )