CODEWITHSUNDEEP
sqlsql-server
user1375481
user1375481

Reputation: 309

Selecting Maximum Value frm the column and adding up multiple columns

I have a table Named "Patients" which looks like

  PatientName    DateOftest    Eye     L1    L2    L3    L4     L5
    Mike         17-02-2009     L      23    25    40    32     30
    Mike         17-02-2009     R      25    30    34    35     24
    Bill         08-03-2006     L      20    24    30    24     25
    Bill         08-03-2006     R      18    25    27    30     24

Now, Iam after results which will select Patient Name and Dateoftest and eye as "Binocular" and "Sum" which is the sum of the highest values of L1,L2,L3,L4,L5 on that Test date..

Results:

  PatientName    DateOftest      Eye          Sum
    Mike         17-02-2009    Binocular      160    
    Bill         08-03-2006    Binocular      130

Here "sum" is sum of Highest values from Left and Right Eye of Mike which is 160 and Bill is 130.

Upvotes: 2

Views: 140

Answers (4)

user2001117
user2001117

Reputation: 3777

This will not search on entire column this will search max value for particular date and particular patient.because we have use group by here.

select PatientName,
       DateOftest,
       Max(L1)+Max(L2)+Max(L3)+Max(L4)+Max(L5) as Sum,
      'Binocular' Eye 
from table 
group by PatientName,DateOftest,Eye

Upvotes: 0

user440595
user440595

Reputation: 

DECLARE @Data AS TABLE
(
    PatientName varchar(10) NOT NULL,
    DateOfTest  date NOT NULL,
    Eye         char(1) NOT NULL,
    L1          tinyint NOT NULL,
    L2          tinyint NOT NULL,
    L3          tinyint NOT NULL,
    L4          tinyint NOT NULL,
    L5          tinyint NOT NULL
)

INSERT @Data
VALUES 
    ('Mike', '20090217', 'L', 23, 25, 40, 32, 30),
    ('Mike', '20090217', 'R', 25, 30, 34, 35, 24),
    ('Bill', '20060308', 'L', 20, 24, 30, 24, 25),
    ('Bill', '20060308', 'R', 18, 25, 27, 30, 24);

SELECT
    d.PatientName,
    d.DateOfTest,
    Eye = 'Binocular',
    [Sum] = MAX(L1) + MAX(L2) + MAX(L3) + MAX(L4) + MAX(L5)
FROM @Data AS d
GROUP BY
    d.PatientName,
    d.DateOfTest;

Output

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

Try this using GROUP BY and MAX:

SELECT PatientName, 
   DateOfTest,
   MAX(L1) + MAX(L2) + MAX(L3) + MAX(L4) + MAX(L5) SummedVal,
   'Binocular' Eye
FROM Patients
GROUP BY PatientName, DateOfTest, Eye

Here's a quick Fiddle.

And please note the difference in using MAX(L1+L2...) vs MAX(L1) + MAX(L2) ... -- the first won't produce your correct results.

Good luck.

Upvotes: 0

hrunting
hrunting

Reputation: 3947

  SELECT PatientName, DateOfTest, "Binocular" AS Eye,
         MAX(L1 + L2 + L3 + L4 + L5) AS [Sum]
    FROM Patients
GROUP BY PatientName, DateOfTest, Eye

Upvotes: 1

Related Questions