December 7, 2014 at 12:13 pm
This is the table named zakatType with some data
CREATE TABLE [dbo].[zakatType](
[idx] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[varchar](20) NOT NULL,
[descrp] [varchar](100) NOT NULL,
[moreInfo] [bit] NULL,
CONSTRAINT [PK_zakatType] PRIMARY KEY CLUSTERED
(
[idx] 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
SET IDENTITY_INSERT [dbo].[zakatType] ON
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (1, N'81101', N'ZAKAT PADI', NULL)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (2, N'81102', N'ZAKAT PERNIAGAAN', 1)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (3, N'81103', N'ZAKAT WANG SIMPANAN', NULL)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (4, N'81104', N'ZAKAT EMAS', NULL)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (5, N'81105', N'ZAKAT TERNAKAN', 1)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (6, N'81106', N'ZAKAT HARTA', NULL)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (7, N'81107', N'ZAKAT PENDAPATAN', NULL)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (8, N'81108', N'ZAKAT SAHAM', NULL)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (9, N'81109', N'ZAKAT KWSP', 1)
INSERT [dbo].[zakatType] ([idx], , [descrp], [moreInfo]) VALUES (10, N'81110', N'QADHA ZAKAT', NULL)
SET IDENTITY_INSERT [dbo].[zakatType] OFF
This is table named totalCount with some data
CREATE TABLE [dbo].[totalCount](
[idx] [int] IDENTITY(1,1) NOT NULL,
[daerahKutipan] [nvarchar](100) NULL,
[payer] [int] NULL,
[zakatType] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[totalCount] ON
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (101, N'3', 6, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (102, N'3', 16, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (103, N'5', 15, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (104, N'5', 15, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (105, N'5', 15, 7)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (106, N'5', 15, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (107, N'8', 10, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (108, N'9', 13, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (109, N'9', 13, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (110, N'9', 13, 2)
INSERT [dbo].[totalCount] ([idx], [daerahKutipan], [payer], [zakatType]) VALUES (111, N'9', 13, 3)
SET IDENTITY_INSERT [dbo].[totalCount] OFF
/*
zakatType is foreign key to zakatType(idx)
*/
I want Group By daerahKutipan. Then, count Payer based zakatType. As a result, my resultset as follow
daerahKutipan | ZAKAT PERNIAGAAN | ZAKAT WANG SIMPANAN | ZAKAT PENDAPATAN
---------------------------------------------------------------------------------------------
3 2 0 0
5 1 0 1
8 1 0 0
9 1 1 0
*payer is Distinct
Please help.
December 7, 2014 at 12:41 pm
Maybe this article will help get you sorted out
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply