I have a report template with the below query that will grab each customerid and display varying information
,avg(orderamount) as [ORDER AVG]
FROM customers cu
LEFT JOIN orders o on cu.customerid = o.customerid
JOIN orderdetails ode on o.orderid = ode.orderid
GROUP BY cu.customerid, cu.city
...but I am going to want to display a dozen or so columns similar to ORDER AVG . The different columns will do a variety of things such as look for data tied to datetimes a week prior predicated on each distinct customerid I establish in my SELECT clause.
For beginners, I am having problems with this COUNT derived field that i need orders that get delivered within the past 2 days for the one distinct customerid in question:
(select count(o.confirmed) from
Orders where o.orderarrived between getdate()-2 and getdate()) as [ORDER CONFIRMED]
..renders error : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, etc
how should i include this in my SELECT lineup , and make sure it is evaluating the o.confirmed and o.orderarrived for the customerid in question?
Thanks for help