CODEWITHSUNDEEP
mysqlsqlsql-serverrow
Elena Khan
Elena Khan

Reputation: 27

SQL: How to find the row with a maximum number of a specific value

I need to find the row containing the most ones in this specific table. the table looks like that and my output is shown just above the given table.

Table and the desired output

I am yet unfamiliar with sql so maybe it is easy to solve but I didn't get any solution so far.

Is there a way to say please search for a specific value and sum the found values up and the output should be given in an extra column?

Upvotes: 0

Views: 84

Answers (3)

Ilyes
Ilyes

Reputation: 14928

You can use CASE OR IIF if you are on SQL Server 2012+ as:

CREATE TABLE X (
    A INT,
    B INT,
    C INT,
    D INT,
    E INT,
    F INT
    );

INSERT INTO X VALUES
(1, 0, 0, 1, 0, 1),
(2, 0, 1, 2, 1, 0),
(1, 0, 1, 0, 1, 0);

SELECT X.*,
       IIF(A = 1, A, 0) +
       IIF(B = 1, B, 0) +
       IIF(C = 1, C, 0) +
       IIF(D = 1, D, 0) +
       IIF(E = 1, E, 0) +
       IIF(F = 1, F, 0) AS [Σ]  
FROM X;

Results:

+---+---+---+---+---+---+---+
| A | B | C | D | E | F | Σ |
+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 1 | 0 | 1 | 3 |
| 2 | 0 | 1 | 2 | 1 | 0 | 2 |
| 1 | 0 | 1 | 0 | 1 | 0 | 3 |
+---+---+---+---+---+---+---+

Upvotes: 0

ravioli
ravioli

Reputation: 3833

Assuming your column data types are numeric, you can use this:

SELECT 
  CASE WHEN col1 = 1 THEN '*' ELSE '' END || 
  CASE WHEN col2 = 1 THEN '*' ELSE '' END ||
  ...
  AS RowValueSearchString

Then just use a LEN/CHAR_LENGTH function (depending on whatever DB you have) on RowValueSearchString to find the length of the string above. The concatenated string's length will give you the number of occurrences of the value you're looking for within the row.

Upvotes: 0

Slav Podolsky
Slav Podolsky

Reputation: 36

looks pretty basic stuff ... hope this helps :

SELECT X.*,
       CASE WHEN X.A=1 THEN 1 ELSE 0 END +
       CASE WHEN X.B=1 THEN 1 ELSE 0 END +
       CASE WHEN X.C=1 THEN 1 ELSE 0 END +
       CASE WHEN X.D=1 THEN 1 ELSE 0 END +
       CASE WHEN X.E=1 THEN 1 ELSE 0 END +
       CASE WHEN X.F=1 THEN 1 ELSE 0 END AS SUM_ONES  
FROM   X;

Upvotes: 2

Related Questions