SQLTeam.com | Weblogs | Forums

Creating a series of derived aggregate columns tied to distinct customerids


I have a report template with the below query that will grab each customerid and display varying information

SELECT cu.customerid
,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


When you run the subquery, does it return more than 1 row?
If you would please post a create table statement or declare a table variable and inserts to give sample data to work with you may get more helpful responses.