phpmyadmin Question posted in
The official documentation can be found here.

SQL database calculation for null variable using other components – Phpmyadmin

enter image description here

I’m looking to calculate the null variable by using predifined weights on a healthcare calculator and I want the output to be a sum of the variables weights over the total available weights. As you can see below this would hopefully be "sum/8".

CREATE TABLE `trial`.`trial` ( `Name` TEXT NULL , `Age` INT NULL , `BP systolic` INT NULL , `BP diastolic` INT NULL ,`Clinical features of the TIA` TEXT NULL , `Duration of symptoms` INT NULL , `History of diabetes` TEXT NULL , `ABCD² Score for TIA` FLOAT NULL ) ENGINE = InnoDB;

INSERT INTO `trial` (`Name`, `Age`, `BP systolic`, `BP diastolic`, `Clinical features of the TIA`, `Duration of symptoms`, `History of diabetes`, `ABCD² Score for TIA`) VALUES ('Person A', '71', '137', '85', 'Speech disturbance without weakness', '17', 'Yes', NULL);

INSERT INTO `trial` (`Name`, `Age`, `BP systolic`, `BP diastolic`, `Clinical features of the TIA`, `Duration of symptoms`, `History of diabetes`, `ABCD² Score for TIA`) VALUES ('Person B', '92', '125', '78', 'Other symptoms', '43', 'Yes', NULL);
INSERT INTO `trial` (`Name`, `Age`, `BP systolic`, `BP diastolic`, `Clinical features of the TIA`, `Duration of symptoms`, `History of diabetes`, `ABCD² Score for TIA`) VALUES ('Person C', '27', '130', '90', 'Other symptoms', '34', 'No', NULL);

update trial 
set ABCD² Score for TIA = case when ( 
case when Age = >=60 then 1 else 0 end 
+ case when BP systolic = >= 140 then 1 else 0 end 
+ case when BP diastolic = >=90 then 1 else 0 end 
+ case when Clinical features of the TIA = 'Unilateral weakness' then 2 end 
+ case when Clinical features of the TIA = 'Speech disturbance without weakness' then 1 end
+ case when Clinical features of the TIA = 'Other symptoms' then 0 end 
+ case when Duration of symptoms = <10   then 0 end 
+ case when Duration of symptoms = 10-59 then 1 end 
+ case when Duration of symptoms = >= 60 then 2 end
+ case when History of diabetes = 'Yes' then 1 else 0 end
) sum/8 
where ABCD² Score for TIA is null
Tags:

2

Answers


  1. 0

    Yuo don’t need CASE expressions when the values are either 1 or 0. The value of a condition is 1 when it’s true, 0 otherwise. So just use the condition directly.

    And you don’t need the cases to be nested inside another case.

    You need backticks around all the column names that contain spaces.

    UPDATE trial
SET `ABCD² Score for TIA` = (
    Age >= 60
    + `BP systolic` >= 140
    + `BP diastolic >= 90`
    + (case `Clinical features of the TIA`
            WHEN 'Unilateral weakness' then 2
            WHEN 'Speech disturbance without weakness' THEN 1
            ELSE 0
       END)
    + (case when `Duration of symptoms` BETWEEN 10 AND 59 THEN 1
            when `Duration of symptoms` >= 60 then 2
            ELSE 0 
       END)
    + `History of diabetes` = 'Yes')
WHERE `ABCD² Score for TIA` IS NULL
    Login or Signup to reply.
  2. 0

    You must fix your CASE expressions and use MySql’s feature to evaluate boolean expressions as 1 or 0:

    update trial 
set `ABCD² Score for TIA` = ( 
  (Age >= 60) + (`BP systolic` >= 140) + (`BP diastolic` >= 90) +
  case `Clinical features of the TIA`
    when 'Unilateral weakness' then 2 
    when 'Speech disturbance without weakness' then 1 
    when 'Other symptoms' then 0 
  end +  
  case
    when `Duration of symptoms` >= 60 then 2
    when `Duration of symptoms` >= 10 then 1
    when `Duration of symptoms` < 10 then 0
  end + 
  (`History of diabetes` = 'Yes')
) / 8 
where `ABCD² Score for TIA` is null

    See the demo.
    Results:

    > Name     | Age | BP systolic | BP diastolic | Clinical features of the TIA        | Duration of symptoms | History of diabetes | ABCD² Score for TIA
> :------- | --: | ----------: | -----------: | :---------------------------------- | -------------------: | :------------------ | -------------------:
> Person A |  71 |         137 |           85 | Speech disturbance without weakness |                   17 | Yes                 |                  0.5
> Person B |  92 |         125 |           78 | Other symptoms                      |                   43 | Yes                 |                0.375
> Person C |  27 |         130 |           90 | Other symptoms                      |                   34 | No                  |                 0.25
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search