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
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);
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
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))
You can also take help from here http://help4assignment.co.uk
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.
use ; instead of , in the code it will solve this.
Please show example using ;
hi hope this helps
solved = NO
create tables insert data script
drop table dbo.orders
drop table dbo.shipments
create table dbo.orders
(
OrderID int NOT NULL,
CustomerID varchar(5) NOT NULL,
sourceid uniqueidentifier NOT NULL,
Status varchar(50) null,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
create table dbo.shipments
(
evID 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)
);
INSERT INTO dbo.orders (OrderID, CustomerID, sourceid, Status, ordershipped, orderarrived)
VALUES
(1001, 'C001', NEWID(), 'Processing', NULL, NULL),
(1002, 'C002', NEWID(), 'Shipped', '2026-05-10', NULL),
(1003, 'C003', NEWID(), 'Confirmed', '2026-05-05', '2026-05-12'),
(1004, 'C001', NEWID(), 'Cancelled', NULL, NULL),
(1005, 'C004', NEWID(), 'Confirmed', '2026-04-28', '2026-05-03');
INSERT INTO dbo.shipments (evID, shipdate, CustomerID, location)
VALUES
('S001', '2026-05-16', 'C002', 'Hyderabad'),
('S002', '2026-05-05', 'C003', 'Bangalore'),
('S003', '2026-04-28', 'C004', 'Chennai'),
('S004', '2026-05-11', 'C001', 'Delhi'),
('S005', '2026-05-16', 'C003', 'Mumbai');
Data
T-sql
DECLARE @now DATE;
SET @now = CAST(GETDATE() AS DATE);
SELECT
COALESCE(o.customerid, 'Total') AS CUSTID,
SUM(CASE WHEN o.status = 'Confirmed' THEN 1 ELSE 0 END) AS READY,
SUM(CASE WHEN sh.shipdate = @now THEN 1 ELSE 0 END) AS [SHIPPING TODAY]
FROM Orders o
LEFT JOIN Shipments sh
ON o.customerid = sh.customerid
GROUP BY ROLLUP(o.customerid);
Result
