February 17, 2017 at 9:49 am
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
February 17, 2017 at 12:47 pm
Zososql - Friday, February 17, 2017 9:49 AMIm 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
Add a pair of parentheses around that select that's inside your sum.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 18, 2017 at 12:49 pm
OK thanks... again 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
...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
z
February 19, 2017 at 12:08 pm
Zososql - Saturday, February 18, 2017 12:49 PMOK thanks... again the problem lies with each unique customerID has to produce a result with the above formula, the output needs to be something similar toCUSTID HOLDING
1OO 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
z
Maybe you need to make a change to the HOLDING field formula... Your using a correlated subquery, and you're using a table alias that duplicates one that's already in use, which may or may not cause trouble. Try this and let me know what results you get.
,[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)
)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 19, 2017 at 12:38 pm
Thanks Steve, let me see if this is on the right track, which it could be . Appreciate your insight.
Z
February 19, 2017 at 2:18 pm
Steve , I reassign aliases to the orders table reference in the subquery, and try this subquery, i get null for the Total summary (on the rollup), tried doing IsNull and NullIf to convert the nulls to 0s
??
Z
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply