need help to count record based on group

• Qira

  SSCommitted

  Points: 1930

  More actions

  December 10, 2014 at 9:00 am

  #313662

  This my table named myData

  CREATE TABLE [dbo].[myData](

  [idx] [int] NULL,

  [paymentMethod] [nvarchar](200) NULL,

  [daerahKutipan] [int] NULL,

  [payer] [int] NULL,

  CONSTRAINT [myData_UQ1] UNIQUE NONCLUSTERED

  (

  [paymentMethod] ASC,

  [daerahKutipan] ASC,

  [payer] 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

  *My table schema and full data as attachment.

  I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan.

  So far, my query as follow

  select paymentMethod,

  COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,

  COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,

  COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,

  COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,

  COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,

  COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,

  COUNT(CASE WHEN daerahKutipan = 8 THEN payer ELSE 0 END) figure_Jempol,

  COUNT(CASE WHEN daerahKutipan = 9 THEN payer ELSE 0 END) figure_Gemas,

  COUNT(CASE WHEN daerahKutipan = 10 THEN payer ELSE 0 END) figure_Nilai,

  COUNT(CASE WHEN daerahKutipan = 13 THEN payer ELSE 0 END) figure_Seremban2,

  COUNT(CASE WHEN daerahKutipan = 14 THEN payer ELSE 0 END) figure_AgentKutipan,

  COUNT(CASE WHEN daerahKutipan = 15 THEN payer ELSE 0 END) figure_Senawang

  from myData

  Group By paymentMethod;

  My result as follow,

  paymentMethodfigure_Serembanfigure_KualaPilahfigure_PortDicksonfigure_Jelebufigure_Tampinfigure_Rembaufigure_Jempolfigure_Gemasfigure_Nilaifigure_Seremban2figure_AgentKutipanfigure_Senawang

  A/MASJID639563956395639563956395639563956395639563956395

  AGEN KORPORAT777777777777777777777777

  BANKCARD282828282828282828282828

  BIMB336336336336336336336336336336336336

  BK RAKYAT111111111111

  BMMB212121212121212121212121

  BPM111111111111111111111111

  BSN929292929292929292929292

  EON BANK444444444444

  IB-AFFIN222222222222

  IB-AMGB222222222222222222222222

  IB-BCB425425425425425425425425425425425425

  IB-BIMB929292929292929292929292

  IB-EON999999999999

  IB-HSBC101010101010101010101010

  IB-MB2U998998998998998998998998998998998998

  IB-OCBC666666666666

  IB-PUBLIC110110110110110110110110110110110110

  IB-RHB515151515151515151515151

  KAUNTER355983559835598355983559835598355983559835598355983559835598

  MBB757575757575757575757575

  P/Gaji333333333333333333333333

  PH-B-MB2U111111111111

  POS151151151151151151151151151151151151

  POS MSIA216216216216216216216216216216216216

  POS-Online111111111111

  SMS-BIMB111111111111

  TAKAFUL MSIA131313131313131313131313

  ZAKAT ONLINE333333333333

  Looks like the number of payer is WRONG.

  Please help

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  December 10, 2014 at 9:13 am

  #1763829

  select paymentMethod,

  COUNT(CASE WHEN daerahKutipan = 1 THEN payer END) figure_Seremban,

  COUNT(CASE WHEN daerahKutipan = 3 THEN payer END) figure_KualaPilah,

  COUNT(CASE WHEN daerahKutipan = 4 THEN payer END) figure_PortDickson,

  COUNT(CASE WHEN daerahKutipan = 5 THEN payer END) figure_Jelebu,

  COUNT(CASE WHEN daerahKutipan = 6 THEN payer END) figure_Tampin,

  COUNT(CASE WHEN daerahKutipan = 7 THEN payer END) figure_Rembau,

  COUNT(CASE WHEN daerahKutipan = 8 THEN payer END) figure_Jempol,

  COUNT(CASE WHEN daerahKutipan = 9 THEN payer END) figure_Gemas,

  COUNT(CASE WHEN daerahKutipan = 10 THEN payer END) figure_Nilai,

  COUNT(CASE WHEN daerahKutipan = 13 THEN payer END) figure_Seremban2,

  COUNT(CASE WHEN daerahKutipan = 14 THEN payer END) figure_AgentKutipan,

  COUNT(CASE WHEN daerahKutipan = 15 THEN payer END) figure_Senawang

  from myData

  Group By paymentMethod;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537