How to count all values of columns where two conditions are satisfied using mysql?
0
I have two tables one is "invoices" and other is "invoice_items".so i want to generate report using these two tables.
php mysql
asked Nov 19 '18 at 20:33
becoolbecool
245
add a comment |
0
I have two tables one is "invoices" and other is "invoice_items".so i want to generate report using these two tables.
php mysql
asked Nov 19 '18 at 20:33
becoolbecool
245
What did you try so far?
– Kurohige
Nov 19 '18 at 20:39
@Kurohige updated sir.plz check my question again
– becool
Nov 19 '18 at 20:47
add a comment |
0
0
0
I have two tables one is "invoices" and other is "invoice_items".so i want to generate report using these two tables.
php mysql
asked Nov 19 '18 at 20:33
becoolbecool
245
I have two tables one is "invoices" and other is "invoice_items".so i want to generate report using these two tables.
php mysql
php mysql
asked Nov 19 '18 at 20:33
becoolbecool
245
asked Nov 19 '18 at 20:33
becoolbecool
245
edited Nov 20 '18 at 14:45
becool
asked Nov 19 '18 at 20:33
becoolbecool
245
asked Nov 19 '18 at 20:33
becoolbecool
245
asked Nov 19 '18 at 20:33
becoolbecool
245
245
What did you try so far?
– Kurohige
Nov 19 '18 at 20:39
@Kurohige updated sir.plz check my question again
– becool
Nov 19 '18 at 20:47
add a comment |
What did you try so far?
– Kurohige
Nov 19 '18 at 20:39
@Kurohige updated sir.plz check my question again
– becool
Nov 19 '18 at 20:47
What did you try so far?
– Kurohige
Nov 19 '18 at 20:39
What did you try so far?
– Kurohige
Nov 19 '18 at 20:39
@Kurohige updated sir.plz check my question again
– becool
Nov 19 '18 at 20:47
@Kurohige updated sir.plz check my question again
– becool
Nov 19 '18 at 20:47
add a comment |
2 Answers
2
active
oldest
votes
1
This should do the trick...please have a look on CASE WHEN
select i.Date, i.No,sum(CASE WHEN t.VAT<>'no' THEN
amount ELSE 0 END) as Excluding_VAT,
sum(t.amt_vat)as vatamount,
sum(CASE WHEN t.VAT='no' THEN amount ELSE 0 END) as NonVat,
sum(t.amt_vat+t.amount)as totamt
from a i join b t on i.ID=t.ID
where i.Date between '1991-11-18' and '1995-11-19'
group by i.ID,i.No,i.Date
edited Nov 20 '18 at 15:13
becool
245
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
@Kurohige.Thank you sir.its working fine
– becool
Nov 20 '18 at 3:53
@becool have a look on number_format (w3schools.com/php/func_string_number_format.asp) php function
– Kurohige
Nov 20 '18 at 8:20
@becool I tried <td>".number_format($row['exclvat'],2)."</td> and worked fine on LibreOffice...
– Kurohige
Nov 20 '18 at 9:05
@becool this answer is correct and should work. +1
– Madhur Bhaiya
Nov 20 '18 at 9:12
@becool that is excel/libreoffice calc's auto formatting feature. It is not concerned with PHP or MySQL code. You will have to turn off some setting in there.
– Madhur Bhaiya
Nov 20 '18 at 9:33
|
show 4 more comments
0
this is for Sale_Value_Excluding_VAT
SELECT SUM(amount) FROM (select amount from invoice_items join invoices on invoices.invoiceid=invoice_items.invoiceid
where includevat=TRUE) AS T
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
add a 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%2f53382219%2fhow-to-count-all-values-of-columns-where-two-conditions-are-satisfied-using-mysq%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
This should do the trick...please have a look on CASE WHEN
select i.Date, i.No,sum(CASE WHEN t.VAT<>'no' THEN
amount ELSE 0 END) as Excluding_VAT,
sum(t.amt_vat)as vatamount,
sum(CASE WHEN t.VAT='no' THEN amount ELSE 0 END) as NonVat,
sum(t.amt_vat+t.amount)as totamt
from a i join b t on i.ID=t.ID
where i.Date between '1991-11-18' and '1995-11-19'
group by i.ID,i.No,i.Date
edited Nov 20 '18 at 15:13
becool
245
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
@Kurohige.Thank you sir.its working fine
– becool
Nov 20 '18 at 3:53
@becool have a look on number_format (w3schools.com/php/func_string_number_format.asp) php function
– Kurohige
Nov 20 '18 at 8:20
@becool I tried <td>".number_format($row['exclvat'],2)."</td> and worked fine on LibreOffice...
– Kurohige
Nov 20 '18 at 9:05
@becool this answer is correct and should work. +1
– Madhur Bhaiya
Nov 20 '18 at 9:12
@becool that is excel/libreoffice calc's auto formatting feature. It is not concerned with PHP or MySQL code. You will have to turn off some setting in there.
– Madhur Bhaiya
Nov 20 '18 at 9:33
|
show 4 more comments
1
This should do the trick...please have a look on CASE WHEN
select i.Date, i.No,sum(CASE WHEN t.VAT<>'no' THEN
amount ELSE 0 END) as Excluding_VAT,
sum(t.amt_vat)as vatamount,
sum(CASE WHEN t.VAT='no' THEN amount ELSE 0 END) as NonVat,
sum(t.amt_vat+t.amount)as totamt
from a i join b t on i.ID=t.ID
where i.Date between '1991-11-18' and '1995-11-19'
group by i.ID,i.No,i.Date
edited Nov 20 '18 at 15:13
becool
245
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
@Kurohige.Thank you sir.its working fine
– becool
Nov 20 '18 at 3:53
@becool have a look on number_format (w3schools.com/php/func_string_number_format.asp) php function
– Kurohige
Nov 20 '18 at 8:20
@becool I tried <td>".number_format($row['exclvat'],2)."</td> and worked fine on LibreOffice...
– Kurohige
Nov 20 '18 at 9:05
@becool this answer is correct and should work. +1
– Madhur Bhaiya
Nov 20 '18 at 9:12
@becool that is excel/libreoffice calc's auto formatting feature. It is not concerned with PHP or MySQL code. You will have to turn off some setting in there.
– Madhur Bhaiya
Nov 20 '18 at 9:33
|
show 4 more comments
1
1
1
This should do the trick...please have a look on CASE WHEN
select i.Date, i.No,sum(CASE WHEN t.VAT<>'no' THEN
amount ELSE 0 END) as Excluding_VAT,
sum(t.amt_vat)as vatamount,
sum(CASE WHEN t.VAT='no' THEN amount ELSE 0 END) as NonVat,
sum(t.amt_vat+t.amount)as totamt
from a i join b t on i.ID=t.ID
where i.Date between '1991-11-18' and '1995-11-19'
group by i.ID,i.No,i.Date
edited Nov 20 '18 at 15:13
becool
245
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
This should do the trick...please have a look on CASE WHEN
select i.Date, i.No,sum(CASE WHEN t.VAT<>'no' THEN
amount ELSE 0 END) as Excluding_VAT,
sum(t.amt_vat)as vatamount,
sum(CASE WHEN t.VAT='no' THEN amount ELSE 0 END) as NonVat,
sum(t.amt_vat+t.amount)as totamt
from a i join b t on i.ID=t.ID
where i.Date between '1991-11-18' and '1995-11-19'
group by i.ID,i.No,i.Date
edited Nov 20 '18 at 15:13
becool
245
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
edited Nov 20 '18 at 15:13
becool
245
edited Nov 20 '18 at 15:13
becool
245
edited Nov 20 '18 at 15:13
becool
245
245
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
answered Nov 19 '18 at 21:24
KurohigeKurohige
5981520
5981520
@Kurohige.Thank you sir.its working fine
– becool
Nov 20 '18 at 3:53
@becool have a look on number_format (w3schools.com/php/func_string_number_format.asp) php function
– Kurohige
Nov 20 '18 at 8:20
@becool I tried <td>".number_format($row['exclvat'],2)."</td> and worked fine on LibreOffice...
– Kurohige
Nov 20 '18 at 9:05
@becool this answer is correct and should work. +1
– Madhur Bhaiya
Nov 20 '18 at 9:12
@becool that is excel/libreoffice calc's auto formatting feature. It is not concerned with PHP or MySQL code. You will have to turn off some setting in there.
– Madhur Bhaiya
Nov 20 '18 at 9:33
|
show 4 more comments
@Kurohige.Thank you sir.its working fine
– becool
Nov 20 '18 at 3:53
@becool have a look on number_format (w3schools.com/php/func_string_number_format.asp) php function
– Kurohige
Nov 20 '18 at 8:20
@becool I tried <td>".number_format($row['exclvat'],2)."</td> and worked fine on LibreOffice...
– Kurohige
Nov 20 '18 at 9:05
@becool this answer is correct and should work. +1
– Madhur Bhaiya
Nov 20 '18 at 9:12
@becool that is excel/libreoffice calc's auto formatting feature. It is not concerned with PHP or MySQL code. You will have to turn off some setting in there.
– Madhur Bhaiya
Nov 20 '18 at 9:33
@Kurohige.Thank you sir.its working fine
– becool
Nov 20 '18 at 3:53
@Kurohige.Thank you sir.its working fine
– becool
Nov 20 '18 at 3:53
@becool have a look on number_format (w3schools.com/php/func_string_number_format.asp) php function
– Kurohige
Nov 20 '18 at 8:20
@becool have a look on number_format (w3schools.com/php/func_string_number_format.asp) php function
– Kurohige
Nov 20 '18 at 8:20
@becool I tried <td>".number_format($row['exclvat'],2)."</td> and worked fine on LibreOffice...
– Kurohige
Nov 20 '18 at 9:05
@becool I tried <td>".number_format($row['exclvat'],2)."</td> and worked fine on LibreOffice...
– Kurohige
Nov 20 '18 at 9:05
@becool this answer is correct and should work. +1
– Madhur Bhaiya
Nov 20 '18 at 9:12
@becool this answer is correct and should work. +1
– Madhur Bhaiya
Nov 20 '18 at 9:12
@becool that is excel/libreoffice calc's auto formatting feature. It is not concerned with PHP or MySQL code. You will have to turn off some setting in there.
– Madhur Bhaiya
Nov 20 '18 at 9:33
@becool that is excel/libreoffice calc's auto formatting feature. It is not concerned with PHP or MySQL code. You will have to turn off some setting in there.
– Madhur Bhaiya
Nov 20 '18 at 9:33
|
show 4 more comments
0
this is for Sale_Value_Excluding_VAT
SELECT SUM(amount) FROM (select amount from invoice_items join invoices on invoices.invoiceid=invoice_items.invoiceid
where includevat=TRUE) AS T
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
add a comment |
0
this is for Sale_Value_Excluding_VAT
SELECT SUM(amount) FROM (select amount from invoice_items join invoices on invoices.invoiceid=invoice_items.invoiceid
where includevat=TRUE) AS T
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
add a comment |
0
0
0
this is for Sale_Value_Excluding_VAT
SELECT SUM(amount) FROM (select amount from invoice_items join invoices on invoices.invoiceid=invoice_items.invoiceid
where includevat=TRUE) AS T
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
this is for Sale_Value_Excluding_VAT
SELECT SUM(amount) FROM (select amount from invoice_items join invoices on invoices.invoiceid=invoice_items.invoiceid
where includevat=TRUE) AS T
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
answered Nov 19 '18 at 21:15
Naor TedgiNaor Tedgi
1,115819
1,115819
add a comment |
add a 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%2f53382219%2fhow-to-count-all-values-of-columns-where-two-conditions-are-satisfied-using-mysq%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
What did you try so far?
– Kurohige
Nov 19 '18 at 20:39
@Kurohige updated sir.plz check my question again
– becool
Nov 19 '18 at 20:47