mysqli every time work only else condition but not if condition
0
Here Is My Query
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
Not work
IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
Every time work only else condation SUM(gpa)/count(subject_name
Not work subject_type=1,SUM(gpa)-2/count(subject_name)
My Table
Result : gpa = 5+8+4+6
= 23
But subject_type = 1 so ,minus -2 (not work)
= 21 (Not work)
Final Gpa = 21/count(subject_name)
php mysql codeigniter mysqli
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
|
show 5 more comments
0
Here Is My Query
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
Not work
IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
Every time work only else condation SUM(gpa)/count(subject_name
Not work subject_type=1,SUM(gpa)-2/count(subject_name)
My Table
Result : gpa = 5+8+4+6
= 23
But subject_type = 1 so ,minus -2 (not work)
= 21 (Not work)
Final Gpa = 21/count(subject_name)
php mysql codeigniter mysqli
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
Why are you dividing bycount(subject_name)?
– Nick
Nov 13 '18 at 9:18
total subjectlike = 7 or 9 ...
– Hasan Sheikh
Nov 13 '18 at 9:19
So you're trying to get an average?
– Nick
Nov 13 '18 at 9:29
yes i try(total_gpa)/total_subjectif have anyextra subjectthenminus (-2)form totalgpa
– Hasan Sheikh
Nov 13 '18 at 9:34
So do you subtract 2 if ANY subject hassubject_type=1or subtract 2 for EVERY subject that hassubject_type=1?
– Nick
Nov 13 '18 at 9:35
|
show 5 more comments
0
0
0
Here Is My Query
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
Not work
IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
Every time work only else condation SUM(gpa)/count(subject_name
Not work subject_type=1,SUM(gpa)-2/count(subject_name)
My Table
Result : gpa = 5+8+4+6
= 23
But subject_type = 1 so ,minus -2 (not work)
= 21 (Not work)
Final Gpa = 21/count(subject_name)
php mysql codeigniter mysqli
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
Here Is My Query
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
Not work
IF(subject_type=1,SUM(gpa)-2/count(subject_name),SUM(gpa)/count(subject_name))
Every time work only else condation SUM(gpa)/count(subject_name
Not work subject_type=1,SUM(gpa)-2/count(subject_name)
My Table
Result : gpa = 5+8+4+6
= 23
But subject_type = 1 so ,minus -2 (not work)
= 21 (Not work)
Final Gpa = 21/count(subject_name)
php mysql codeigniter mysqli
php mysql codeigniter mysqli
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
edited Nov 25 '18 at 5:18
Undo♦
22.9k2087112
22.9k2087112
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
asked Nov 13 '18 at 9:10
Hasan SheikhHasan Sheikh
4912
4912
Why are you dividing bycount(subject_name)?
– Nick
Nov 13 '18 at 9:18
total subjectlike = 7 or 9 ...
– Hasan Sheikh
Nov 13 '18 at 9:19
So you're trying to get an average?
– Nick
Nov 13 '18 at 9:29
yes i try(total_gpa)/total_subjectif have anyextra subjectthenminus (-2)form totalgpa
– Hasan Sheikh
Nov 13 '18 at 9:34
So do you subtract 2 if ANY subject hassubject_type=1or subtract 2 for EVERY subject that hassubject_type=1?
– Nick
Nov 13 '18 at 9:35
|
show 5 more comments
Why are you dividing bycount(subject_name)?
– Nick
Nov 13 '18 at 9:18
total subjectlike = 7 or 9 ...
– Hasan Sheikh
Nov 13 '18 at 9:19
So you're trying to get an average?
– Nick
Nov 13 '18 at 9:29
yes i try(total_gpa)/total_subjectif have anyextra subjectthenminus (-2)form totalgpa
– Hasan Sheikh
Nov 13 '18 at 9:34
So do you subtract 2 if ANY subject hassubject_type=1or subtract 2 for EVERY subject that hassubject_type=1?
– Nick
Nov 13 '18 at 9:35
Why are you dividing by
count(subject_name)?– Nick
Nov 13 '18 at 9:18
Why are you dividing by
count(subject_name)?– Nick
Nov 13 '18 at 9:18
total subject
like = 7 or 9 ...– Hasan Sheikh
Nov 13 '18 at 9:19
total subject
like = 7 or 9 ...– Hasan Sheikh
Nov 13 '18 at 9:19
So you're trying to get an average?
– Nick
Nov 13 '18 at 9:29
So you're trying to get an average?
– Nick
Nov 13 '18 at 9:29
yes i try
(total_gpa)/total_subject if have any extra subject then minus (-2) form total gpa– Hasan Sheikh
Nov 13 '18 at 9:34
yes i try
(total_gpa)/total_subject if have any extra subject then minus (-2) form total gpa– Hasan Sheikh
Nov 13 '18 at 9:34
So do you subtract 2 if ANY subject has
subject_type=1 or subtract 2 for EVERY subject that has subject_type=1?– Nick
Nov 13 '18 at 9:35
So do you subtract 2 if ANY subject has
subject_type=1 or subtract 2 for EVERY subject that has subject_type=1?– Nick
Nov 13 '18 at 9:35
|
show 5 more comments
1 Answer
1
active
oldest
votes
0
If there can be only one extra subject (i.e. subject with subject_type=1) and all other subjects have subject_type=0, you can use MAX(subject_type) to determine if a student took an extra subject. This query should do what you want (note that you also need () around SUM(gpa)-2):
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(MAX(subject_type)=1,(SUM(gpa)-2)/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
I've created a simplified demo at SQLFiddle.
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
don't nick mind can i get your facebook account link .
– Hasan Sheikh
Nov 13 '18 at 10:07
@HasanSheikh I'm not on facebook. SO is my social network.
– Nick
Nov 13 '18 at 10:09
any Communication link
– Hasan Sheikh
Nov 13 '18 at 10:11
any Communication linklike skype or othersplease
– Hasan Sheikh
Nov 13 '18 at 10:16
@HasanSheikh sorry this is all I have
– Nick
Nov 13 '18 at 10:17
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53277421%2fmysqli-every-time-work-only-else-condition-but-not-if-condition%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
If there can be only one extra subject (i.e. subject with subject_type=1) and all other subjects have subject_type=0, you can use MAX(subject_type) to determine if a student took an extra subject. This query should do what you want (note that you also need () around SUM(gpa)-2):
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(MAX(subject_type)=1,(SUM(gpa)-2)/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
I've created a simplified demo at SQLFiddle.
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
don't nick mind can i get your facebook account link .
– Hasan Sheikh
Nov 13 '18 at 10:07
@HasanSheikh I'm not on facebook. SO is my social network.
– Nick
Nov 13 '18 at 10:09
any Communication link
– Hasan Sheikh
Nov 13 '18 at 10:11
any Communication linklike skype or othersplease
– Hasan Sheikh
Nov 13 '18 at 10:16
@HasanSheikh sorry this is all I have
– Nick
Nov 13 '18 at 10:17
|
show 1 more comment
0
If there can be only one extra subject (i.e. subject with subject_type=1) and all other subjects have subject_type=0, you can use MAX(subject_type) to determine if a student took an extra subject. This query should do what you want (note that you also need () around SUM(gpa)-2):
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(MAX(subject_type)=1,(SUM(gpa)-2)/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
I've created a simplified demo at SQLFiddle.
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
don't nick mind can i get your facebook account link .
– Hasan Sheikh
Nov 13 '18 at 10:07
@HasanSheikh I'm not on facebook. SO is my social network.
– Nick
Nov 13 '18 at 10:09
any Communication link
– Hasan Sheikh
Nov 13 '18 at 10:11
any Communication linklike skype or othersplease
– Hasan Sheikh
Nov 13 '18 at 10:16
@HasanSheikh sorry this is all I have
– Nick
Nov 13 '18 at 10:17
|
show 1 more comment
0
0
0
If there can be only one extra subject (i.e. subject with subject_type=1) and all other subjects have subject_type=0, you can use MAX(subject_type) to determine if a student took an extra subject. This query should do what you want (note that you also need () around SUM(gpa)-2):
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(MAX(subject_type)=1,(SUM(gpa)-2)/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
I've created a simplified demo at SQLFiddle.
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
If there can be only one extra subject (i.e. subject with subject_type=1) and all other subjects have subject_type=0, you can use MAX(subject_type) to determine if a student took an extra subject. This query should do what you want (note that you also need () around SUM(gpa)-2):
CREATE VIEW marksheet as
SELECT name as name, student_id as student_id,
roll as roll, class as class,exam_year as exam_year,
subject_name as subject, exam_type as exam_type,
sum(full_mark) as full_mark, sum(getmark) as getmark,
department as department,
IF(SUM(IF(gpa='f' OR gpa='F',-9999,gpa))>=0,
CAST(IF(MAX(subject_type)=1,(SUM(gpa)-2)/count(subject_name),SUM(gpa)/count(subject_name))
AS CHAR), 'F') as total_gpa
FROM mark
GROUP by roll, class, exam_type
I've created a simplified demo at SQLFiddle.
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
answered Nov 13 '18 at 10:01
NickNick
24.9k91735
24.9k91735
don't nick mind can i get your facebook account link .
– Hasan Sheikh
Nov 13 '18 at 10:07
@HasanSheikh I'm not on facebook. SO is my social network.
– Nick
Nov 13 '18 at 10:09
any Communication link
– Hasan Sheikh
Nov 13 '18 at 10:11
any Communication linklike skype or othersplease
– Hasan Sheikh
Nov 13 '18 at 10:16
@HasanSheikh sorry this is all I have
– Nick
Nov 13 '18 at 10:17
|
show 1 more comment
don't nick mind can i get your facebook account link .
– Hasan Sheikh
Nov 13 '18 at 10:07
@HasanSheikh I'm not on facebook. SO is my social network.
– Nick
Nov 13 '18 at 10:09
any Communication link
– Hasan Sheikh
Nov 13 '18 at 10:11
any Communication linklike skype or othersplease
– Hasan Sheikh
Nov 13 '18 at 10:16
@HasanSheikh sorry this is all I have
– Nick
Nov 13 '18 at 10:17
don't nick mind can i get your facebook account link .
– Hasan Sheikh
Nov 13 '18 at 10:07
don't nick mind can i get your facebook account link .
– Hasan Sheikh
Nov 13 '18 at 10:07
@HasanSheikh I'm not on facebook. SO is my social network.
– Nick
Nov 13 '18 at 10:09
@HasanSheikh I'm not on facebook. SO is my social network.
– Nick
Nov 13 '18 at 10:09
any Communication link
– Hasan Sheikh
Nov 13 '18 at 10:11
any Communication link
– Hasan Sheikh
Nov 13 '18 at 10:11
any Communication link
like skype or others please– Hasan Sheikh
Nov 13 '18 at 10:16
any Communication link
like skype or others please– Hasan Sheikh
Nov 13 '18 at 10:16
@HasanSheikh sorry this is all I have
– Nick
Nov 13 '18 at 10:17
@HasanSheikh sorry this is all I have
– Nick
Nov 13 '18 at 10:17
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53277421%2fmysqli-every-time-work-only-else-condition-but-not-if-condition%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why are you dividing by
count(subject_name)?– Nick
Nov 13 '18 at 9:18
total subject
like = 7 or 9 ...– Hasan Sheikh
Nov 13 '18 at 9:19
So you're trying to get an average?
– Nick
Nov 13 '18 at 9:29
yes i try
(total_gpa)/total_subjectif have anyextra subjectthenminus (-2)form totalgpa– Hasan Sheikh
Nov 13 '18 at 9:34
So do you subtract 2 if ANY subject has
subject_type=1or subtract 2 for EVERY subject that hassubject_type=1?– Nick
Nov 13 '18 at 9:35