Grouping, aggregate function issue when joining to another table, Sql Server

Grouping, aggregate function issue when joining to another table

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE [dbo].[Orders](
	[unid] [char](32) NOT NULL,
	[order_reference] [nvarchar](255) NULL,
	[quantity] [smallint] NULL,
	[named_worker] [nvarchar](3) NULL,
CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED 
(
	[unid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[OrderApplicants]    Script Date: 20/06/2016 16:15:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderApplicants](
	[unid] [char](32) NOT NULL,
	[order_unid] [char](32) NULL,
	[agency_name] [nvarchar](255) NULL,
 CONSTRAINT [PK_orderapplicant] PRIMARY KEY CLUSTERED 
(
	[unid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[orderapplicants]  WITH NOCHECK ADD  CONSTRAINT [FK_orders_orderapplicants] FOREIGN KEY([order_unid])
REFERENCES [dbo].[orders] ([unid])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[orderapplicants] NOCHECK CONSTRAINT [FK_orders_orderapplicants]
GO

SET ANSI_PADDING OFF
GO
INSERT [dbo].[Orders] ([unid], [order_reference], [quantity], [named_worker]) VALUES (N'911E23295C23926B80257DEB0030090C', N'BCC-015212', 2, N'Yes')
GO
INSERT [dbo].[OrderApplicants] ([unid], [order_unid], [agency_name]) VALUES (N'1FDA4F9ED6EB5BF580257DEB00307F39', N'911E23295C23926B80257DEB0030090C', N'Extra Personnel')
GO
INSERT [dbo].[OrderApplicants] ([unid], [order_unid], [agency_name]) VALUES (N'23DD55BAF8E4C1C380257DEB00303052', N'911E23295C23926B80257DEB0030090C', N'Extra Personnel')
GO
INSERT [dbo].[OrderApplicants] ([unid], [order_unid], [agency_name]) VALUES (N'67B890412473419E80257FAA0048B0F3', N'911E23295C23926B80257DEB0030090C', N'Caritas Recruitment Ltd')
GO

-- Using order table data only
SELECT 'Order Number'=Case WHEN GROUPING(o.order_reference) = 1 Then 'Grand Total:' ELSE o.order_reference END,
'Orders Raised' = COUNT(distinct o.unid), 
'Named Workers' = SUM(CASE o.named_worker WHEN 'Yes' THEN 1 ELSE 0 END)
FROM orders o
WHERE o.order_reference = 'BCC-015212'
Group By o.order_reference
WITH ROLLUP 
ORDER BY o.order_reference
GO

-- Grouping by agency_name on the orderApplicant table, note how the named workers column is now not accurate
SELECT 'Order Number'=Case WHEN GROUPING(o.order_reference) = 1 Then 'Grand Total:' ELSE o.order_reference END,
'Agency'=Case WHEN GROUPING(oa.agency_name) = 1 Then 'Sub Total:' ELSE oa.agency_name END,
'Orders Raised' = COUNT(distinct o.unid),
'Named Workers' = SUM(CASE o.named_worker WHEN 'Yes' THEN 1 ELSE 0 END)
FROM orders o
Inner Join orderapplicants oa on oa.order_unid = o.unid
WHERE o.order_reference = 'BCC-015212'
Group By o.order_reference,oa.agency_name
WITH ROLLUP 
ORDER BY o.order_reference, oa.agency_name
GO

select * from orders
GO

select * from orderapplicants
GO


run \| edit \| history \| help	0