Group by + Count + Case

• saregama

  Grasshopper

  Points: 10

  More actions

  November 9, 2015 at 5:00 am

  #322138

  Can you please help me here to write sql query on a table with below values to get desired output only using groupby and count and Case statement.

  Table -

  CustFlag

  TaF

  TaT

  saF

  SaF

  RaT

  RaF

  Na T

  Na T

  Desired Output -

  Flag Count

  F - 1

  F & T - 2

  T - 1

  Thanks

• Lowell

  SSC Guru

  Points: 323504

  More actions

  November 9, 2015 at 5:23 am

  #1838512

  what you are looking for is a SUM(CASE construct like this:

  /*

  Cust TotalCount TrueCount FalseCount

  Na 2 2 0

  Ra 2 1 1

  Sa 2 0 2

  Ta 2 1 1

  */

  ;WITH MyTable([Cust],[Flag])

  AS

  (

  SELECT 'Ta','F' UNION ALL

  SELECT 'Ta','T' UNION ALL

  SELECT 'sa','F' UNION ALL

  SELECT 'Sa','F' UNION ALL

  SELECT 'Ra','T' UNION ALL

  SELECT 'Ra','F' UNION ALL

  SELECT 'Na','T' UNION ALL

  SELECT 'Na','T'

  )

  SELECT Cust,COUNT(*) As TotalCount,

  SUM(CASE WHEN [Flag] ='T' THEN 1 ELSE 0 END) As TrueCount,

  SUM(CASE WHEN [Flag] ='F' THEN 1 ELSE 0 END) As FalseCount

  FROM MyTable

  GROUP BY Cust

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 9, 2015 at 5:34 am

  #1838516

  saregama (11/9/2015)

  ---

  Can you please help me here to write sql query on a table with below values to get desired output only using groupby and count and Case statement.

  Table -

  CustFlag

  TaF

  TaT

  saF

  SaF

  RaT

  RaF

  Na T

  Na T

  Desired Output -

  Flag Count

  F - 1

  F & T - 2

  T - 1

  Thanks

  DROP TABLE #Temp

  CREATE TABLE #Temp (Cust CHAR(2), Flag CHAR(1))

  INSERT INTO #Temp VALUES

  ('Ta', 'F'),('Ta', 'T'),('sa', 'F'),('Sa', 'F'),('Ra', 'T'),('Ra', 'F'),('Na', 'T'),('Na', 'T')

  SELECT

  Flag = CASE WHEN MIN_Flag <> MAX_Flag THEN 'F,T' ELSE MIN_Flag END,

  [Count] = COUNT(*)

  FROM (

  SELECT

  Cust,

  MIN_Flag = MIN(Flag),

  MAX_Flag = MAX(Flag)

  FROM #Temp d

  GROUP BY Cust

  ) d

  GROUP BY MIN_Flag, MAX_Flag

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• saregama

  Grasshopper

  Points: 10

  More actions

  November 9, 2015 at 6:18 am

  #1838529

  Hi Lowell and Chris,

  Both your solutions worked but chris on was something i was looking for.

  Thanks for your help guys.

  Regards

  T