SQL count

• Simon Parry

  Ten Centuries

  Points: 1297

  More actions

  September 19, 2010 at 8:46 am

  #227022

  Hi

  I am trying to write a query to count all the telephone numbers with a table

  The below would return 6

  Id Tel1 Tel2 Tel3

  1 01214478953 079733939933

  2 01212339494 049458585858 01238434934

  3 01232345489

  Can any one help at all

  thanks

  Simon

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  September 19, 2010 at 9:13 am

  #1223631

  Try this:

  SET NOCOUNT ON

  DECLARE @PhoneNumbers TABLE

  (

  ID INT IDENTITY(1,1),

  TEL1 BIGINT,

  TEL2 BIGINT,

  TEL3 BIGINT

  )

  INSERT @PhoneNumbers (Tel1, Tel2, Tel3)

  SELECT 01214478953 , 079733939933 , NULL

  UNION ALL SELECT 01212339494 , 049458585858 , 01238434934

  UNION ALL SELECT 01232345489 , NULL , NULL

  SELECT SUM(SumOfRow) AS Total_Telephone_Numbers

  FROM

  (

  SELECT

  CASE WHEN Tel1 IS NOT NULL THEN 1 ELSE 0 END +

  CASE WHEN Tel2 IS NOT NULL THEN 1 ELSE 0 END +

  CASE WHEN Tel3 IS NOT NULL THEN 1 ELSE 0 END

  FROM

  @PhoneNumbers

  ) AS Sub_Table (SumOfRow)

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 19, 2010 at 10:44 am

  #1223652

  here's another way to do the same thing: ColdCoffee thank you for the setup!:

  DECLARE @PhoneNumbers TABLE

  (

  ID INT IDENTITY(1,1),

  TEL1 BIGINT,

  TEL2 BIGINT,

  TEL3 BIGINT

  )

  INSERT @PhoneNumbers (Tel1, Tel2, Tel3)

  SELECT 01214478953 , 079733939933 , NULL

  UNION ALL SELECT 01212339494 , 049458585858 , 01238434934

  UNION ALL SELECT 01232345489 , NULL , NULL

  ;with myCTE AS (SELECT Tel1 FROM @PhoneNumbers UNION ALL

  SELECT Tel2 FROM @PhoneNumbers UNION ALL

  SELECT Tel3 FROM @PhoneNumbers )

  select COUNT(Tel1) from myCTE

  --results = 6,

  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!

• steve-893342

  SSCarpal Tunnel

  Points: 4439

  More actions

  September 19, 2010 at 12:17 pm

  #1223688

  A CROSS APPLY variation. Thanks Cold Coffee for the @PhoneNumbers:-)

  SELECT COUNT(Z.TEL)

  FROM @PhoneNumbers

  CROSS APPLY

  (VALUES

  (TEL1),

  (TEL2),

  (TEL3)

  ) AS Z (TEL)