locked
Multiple colum count RRS feed

  • Question

  • User-1009930070 posted

    Hi

    I have table with 4 players column like player 1, player 2 , player 3 and player 4. I am counting them like this

    SELECT COUNT(*) FROM dbo.Hockey;

    But it is showing 3, In my table i can mis player2 or some time  player 4. how i can count the number of players which will count player1 , 2 , 3, 4.

    when i count it shows 3, but how i can count the other it should show 6

    Player1

    Player2

    Player3

    Player4

    ABc

    Alex

     

     

    Alix

     

    Robert

     

    Zak

     

    Jack

     

    Thanks

    Monday, June 17, 2013 6:53 PM

Answers

  • User-1716253493 posted

    TRY

    (SUM(CASE Player1 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END) +
    SUM(CASE Player2 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END) +
    SUM(CASE Player3 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END) +
    SUM(CASE Player4 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END)) as PLAYERCOUNT 



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 17, 2013 9:26 PM

All replies

  • User-1716253493 posted
    SELECT (COUNT(PLAYER1) + COUNT(PLAYER2) + COUNT(PLAYER3) + COUNT(PLAYER4)) as PLAYERCOUNT FROM dbo.Hockey;

    Monday, June 17, 2013 8:13 PM
  • User-1009930070 posted

    Thanks

     

    I tried but in my table i have two rows in first  row player 1 and player2 field is filled but in second row all players fields are fill but it is counting 8, I just want to count where i have value in player column. if it is null then do not count, your query is working but it is ignoring the null. try to create this table and enter some dummy data, put no value for some players and then count it is counting incorrectly.

    CREATE Table Hockey
    (
    	CustomerRefNumber int IDENTITY NOT NULL Primary Key,
    	Player1 varchar(50) NOT NULL,
    	Player1_Company varchar(100),
    	Player1_Email varchar(60),
    	Player1_Phone varchar(20),
    	Player1_SubTotal decimal(6,2) Default 0.00,
    	Player2 varchar(50),
    	Player2_Company varchar(100),
    	Player3 varchar(50),
    	Player3_Company varchar(100),
    	Player4 varchar(50),
    	Player4_Company varchar(50),
    	DateStamp datetime Default GETDATE()
    )
    
    GO
    
    
    CREATE Procedure dbo.GetCount
    AS
    BEGIN
    	SELECT COUNT(*) FROM dbo.Golf_Teams;
    END
    Go


    I replaced the above count with your statement.

     

    Thanks

     

     

    Monday, June 17, 2013 9:16 PM
  • User-1716253493 posted

    TRY

    (SUM(CASE Player1 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END) +
    SUM(CASE Player2 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END) +
    SUM(CASE Player3 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END) +
    SUM(CASE Player4 WHEN null Then 0 WHEN '' THEN 0 ELSE 1 END)) as PLAYERCOUNT 



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 17, 2013 9:26 PM
  • User-1009930070 posted

    Thanks thats work... i am doing some testing on Development box then i will mark as answer. thanks again

    Monday, June 17, 2013 9:41 PM