Rollup or summary for derived column doesnt produce a grand total

1

I have a field customerid in the orders table that i want to display with some aggregate derived columns , and i want to count the instances a value shows up per customerid. One derived column [SHIPPING TODAY] will have a join into another table via the customerid
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.shipments --sh
(
evID (pk,varchar(5), NOT NULL),
shipdate datetime NULL,
CustomerID varchar(5) NOT NULL,
location nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (evID ASC)
);

SELECT coalesce (cu.customerid,'Total') AS CUSTID
declare @now datetime
set @now = getdate()

[READY] = sum (case when o.status = 'Confirmed' then 1 else 0 end)
,[SHIPPING TODAY] = (select isnull(sum(case when sh.shipdate = @now then 1 else 0 end),0) from shipments sh where o.customerid = sh.customerid)

FROM Orders o

GROUP BY ROLLUP(cu.customerid);

....I get an accurate total for the READY column,the total for the SHIPPING TODAY column adds up to zero? I have tried Grouping Set but to no avail.

Thanks in advance

2 
declare @now datetime
set @now = cast(getdate() as date) /*force time to 00:00*/

SELECT coalesce (cu.customerid,'Total') AS CUSTID,
[READY] = sum (case when o.status = 'Confirmed' then 1 else 0 end)
,[SHIPPING TODAY] = (select isnull(sum(case when sh.shipdate >= @now and sh.shipdate < dateadd(day, 1, @now) 
    then 1 else 0 end),0) from shipments sh where o.customerid = sh.customerid)

FROM Orders o

GROUP BY ROLLUP(cu.customerid);
3

Thanks for help. I inadvertently used wrong alias on customerid for the ROLLUP , should be o.customerid , but wondering how I would get the SHIPPING TODAY column to be included in the grand total calculation on the ROLLUP?
Thanks
SQ

4

I don't have sample data to test with, but I guess throw a SUM() around the subquery:

,[SHIPPING TODAY] = SUM((select isnull(sum(case when sh.shipdate >= @now and sh.shipdate < dateadd(day, 1, @now)
then 1 else 0 end),0) from shipments sh where o.customerid = sh.customerid))

5

You can also take help from here http://help4assignment.co.uk

6

Thanks Scott. It still produces a zero for the grand totals, but i'll post the solution once I get back to working with this query.

7

use ; instead of , in the code it will solve this.

8

Please show example using ;