Derived Column Problems

1

Im needing to create a derived column that takes unique customerids and sums up the instances of certain statuses in an orders table (and eventually do a rollup grandtotal) :

TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
Sourceid (pk uniqueidentifier NOT NULL),
Status varchar(50) null,
Ordershipped datetime NULL,
Orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);

TABLE dbo.customers --cu
(
CustomerID (pk,varchar(5), NOT NULL),
Firstname nvarchar(50) null,
Lastname nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (CustomerID ASC)
);

SELECT coalesce (cu.customerid,'Total') AS CUSTID

,[HOLDING] = sum(select CASE WHEN o.status LIKE '%shipment date schedule%' OR o.status LIKE '%shipment ready%' THEN 1 ELSE 0 end from orders o left join customers cu on o.CustomerID = cu.CustomerID)

FROM CU.customers cu
LEFT JOIN
Orders o

---there will be other table joins, but right now i am isolating this particular segment
ON cu.customerid = o.customerid
GROUP BY ROLLUP(cu.customerid);

...trying to create this HOLDING derived column, I cannot get past the "sum function requires 1 argument" nor the "invalid syntax near select keyword" . I need to evaluate this HOLDING sum for each unique customerID
???
thanks in advance

2 
SELECT coalesce (cu.customerid,'Total') AS CUSTID

,MAX(oj1.[HOLDING]) AS [HOLDING]

FROM CU.customers cu 
LEFT OUTER JOIN (
    SELECT CustomerID, SUM(CASE 
        WHEN status LIKE '%shipment date schedule%' OR 
             status LIKE '%shipment ready%' 
        THEN 1 ELSE 0 END) AS [HOLDING]
    FROM orders
    GROUP BY CustomerID
) AS oj1 ON oj1.CustomerID = cu.CustomerID


GROUP BY ROLLUP(cu.customerid);
3

Thanks for help Scott. Unfortunately, other derived columns are going to be needed to be built among these unique customerids , and the rollup doesnt work with this particular solution (as the addition of all the values in the column are not correct in the Total)
Z

4

Hmm. If the derived table already contains the total, as in this case, then be sure you do a MAX() rather than a SUM() and those should still work OK.

But of course I don't know if that's a solution for you, since I can't analyze code I can't see :slight_smile:

5

... the problem lies with each unique customerID has to produce a result with the above formula, the output needs to be something similar to

CUSTID HOLDING
100 5
101 0
102 15
103 2
Total 22

...when i run this particular statement for the HOLDING column that I show above, I'm getting
CUSTID HOLDING
100 150
101 150
102 150
103 150
Total 150
hope that helps
thanks
s17