SQLTeam.com | Weblogs | Forums

Problem with Subquery, group by


#1

Been looking every where and juggling coding , but cant seem to find an answer: 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 o2.[status] LIKE '%shipment date schedule%' OR o2.[status] LIKE '%shipment ready%' THEN 1 ELSE 0 END
FROM orders o2
WHERE o2.CustomerID = cu.CustomerID)
)
FROM CU.customers cu
LEFT JOIN
Orders o

ON cu.customerid = o.customerid
GROUP BY ROLLUP(cu.customerid);

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
1OO 5
101 0
102 15
103 2
Total 22

thanks
s17


#2

is this the same question as

if so It would be better to continue that thread to keep it in context.