Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    42

    Unanswered: Counting data within each row

    Hello!

    I am using SQL 2000 and I am trying to figure out the best way to summarize data within each row.

    I have a table with 72 columns, each column will have 1 of 3 values(0, 1, or 2). I am trying to count the number of occurences for each value with each row.

    My initial thought was to add three rows to the table and use 3 update statements with a where clause specifing the value for each update. I am not sure if this will get me the results I would like.

    In theory, I just want to count, but within each row instead of each column.

    Any insight would be appreciated.

    Thanks
    Drew

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to normalize your data, splitting those seventy two columns off into a su-table where you can easily manage aggregate calculations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    As blindman suggests, you should probably consider a different design. Just for fun however:

    Code:
    CREATE TABLE tbl
     (foo INT NOT NULL PRIMARY KEY,
      col1 TINYINT CHECK (col1 IN (0,1,2)) NOT NULL,
      col2 TINYINT CHECK (col2 IN (0,1,2)) NOT NULL,
      col3 TINYINT CHECK (col3 IN (0,1,2)) NOT NULL,
      col4 TINYINT CHECK (col4 IN (0,1,2)) NOT NULL,
      col5 TINYINT CHECK (col5 IN (0,1,2)) NOT NULL,
      col6 TINYINT CHECK (col6 IN (0,1,2)) NOT NULL);
     
     INSERT INTO tbl VALUES (100,0,1,2,1,1,2);
     INSERT INTO tbl VALUES (101,1,1,1,1,1,1);
     INSERT INTO tbl VALUES (102,2,1,2,2,2,0);
     INSERT INTO tbl VALUES (103,2,1,0,0,1,2);
     
     SELECT foo,
      6-(x/100 + x%100) NumberOf0s,
      x%100 NumberOf1s,
      x/100 NumberOf2s
     FROM
      (SELECT foo,
       (CASE col1 WHEN 2 THEN 100 ELSE col1 END
       +CASE col2 WHEN 2 THEN 100 ELSE col2 END
       +CASE col3 WHEN 2 THEN 100 ELSE col3 END
       +CASE col4 WHEN 2 THEN 100 ELSE col4 END
       +CASE col5 WHEN 2 THEN 100 ELSE col5 END
       +CASE col6 WHEN 2 THEN 100 ELSE col6 END)
       FROM tbl) t(foo,x);

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, if we are going to engage in MSSQLbation exercises....
    Code:
    select	foo,
    		6 - len(replace (convert(char(1), col1) + convert(char(1), col2) + convert(char(1), col3) + convert(char(1), col4) + convert(char(1), col5) + convert(char(1), col6), 0, '')) as Count0,
    		6 - len(replace (convert(char(1), col1) + convert(char(1), col2) + convert(char(1), col3) + convert(char(1), col4) + convert(char(1), col5) + convert(char(1), col6), 1, '')) as Count1,
    		6 - len(replace (convert(char(1), col1) + convert(char(1), col2) + convert(char(1), col3) + convert(char(1), col4) + convert(char(1), col5) + convert(char(1), col6), 2, '')) as Count2
    from	tbl
    And if you change your datatype to char to begin with, you can leave out all the convert functions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This would also appear to work:
    Code:
    select	*,
    		(PowerSum % 100) as Count0,
    		(PowerSum/100 % 100) as Count1,
    		PowerSum/10000 as Count2
    from	--PowerSums
    		(select	*,
    				power(100, col1)
    				+ power(100, col2)
    				+ power(100, col3)
    				+ power(100, col4)
    				+ power(100, col5)
    				+ power(100, col6) as PowerSum
    		from	tbl) PowerSums
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •