How to get 0 records if a particular month has no records? [duplicate]

This question already has an answer here:

What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

9 answers

Mysql to select month-wise record even if data not exist

2 answers

I have query, which shows last 12 month record, it is working fine for me, but i want to show that month also which have no records, if there is no records for the particular month then i want to show 0 records for that month, here i have added my query for that

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created, 

       MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month, 

       `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

       SUM(amount_after_fees) as total FROM `transaction` 

WHERE `record_owner_user_id` = '50' AND `is_one_time_purchase` = 'Y' 

       AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24"

       and CONVERT_TZ(created,"+00:00","-05:00") >= 

       Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` ORDER BY `id` ASC

Can anyone please help me how can i resolve this issue ?

edited Nov 26 '18 at 12:14

Martin

13.2k53782

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

marked as duplicate by Nick, O. Jones mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 26 '18 at 12:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

You could perhaps RIGHT JOIN to a sub-query with the 12 months. Then group on the month from the sub-query.

– LukStorms
Nov 26 '18 at 12:36

add a comment |

This question already has an answer here:

What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

9 answers

Mysql to select month-wise record even if data not exist

2 answers

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created, 

       MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month, 

       `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

       SUM(amount_after_fees) as total FROM `transaction` 

WHERE `record_owner_user_id` = '50' AND `is_one_time_purchase` = 'Y' 

       AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24"

       and CONVERT_TZ(created,"+00:00","-05:00") >= 

       Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` ORDER BY `id` ASC

Can anyone please help me how can i resolve this issue ?

edited Nov 26 '18 at 12:14

Martin

13.2k53782

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

marked as duplicate by Nick, O. Jones mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 26 '18 at 12:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

You could perhaps RIGHT JOIN to a sub-query with the 12 months. Then group on the month from the sub-query.

– LukStorms
Nov 26 '18 at 12:36

add a comment |

This question already has an answer here:

What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

9 answers

Mysql to select month-wise record even if data not exist

2 answers

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created, 

       MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month, 

       `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

       SUM(amount_after_fees) as total FROM `transaction` 

WHERE `record_owner_user_id` = '50' AND `is_one_time_purchase` = 'Y' 

       AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24"

       and CONVERT_TZ(created,"+00:00","-05:00") >= 

       Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` ORDER BY `id` ASC

Can anyone please help me how can i resolve this issue ?

edited Nov 26 '18 at 12:14

Martin

13.2k53782

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

This question already has an answer here:

What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

9 answers

Mysql to select month-wise record even if data not exist

2 answers

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created, 

       MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month, 

       `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

       SUM(amount_after_fees) as total FROM `transaction` 

WHERE `record_owner_user_id` = '50' AND `is_one_time_purchase` = 'Y' 

       AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24"

       and CONVERT_TZ(created,"+00:00","-05:00") >= 

       Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` ORDER BY `id` ASC

Can anyone please help me how can i resolve this issue ?

This question already has an answer here:

What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

9 answers

Mysql to select month-wise record even if data not exist

2 answers

mysql mysqli

edited Nov 26 '18 at 12:14

Martin

13.2k53782

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

edited Nov 26 '18 at 12:14

Martin

13.2k53782

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

edited Nov 26 '18 at 12:14

Martin

13.2k53782

edited Nov 26 '18 at 12:14

Martin

13.2k53782

edited Nov 26 '18 at 12:14

Martin

13.2k53782

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

asked Nov 26 '18 at 12:11

Nikul Panchal

13611

marked as duplicate by Nick, O. Jones mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 26 '18 at 12:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by Nick, O. Jones mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 26 '18 at 12:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

You could perhaps RIGHT JOIN to a sub-query with the 12 months. Then group on the month from the sub-query.

– LukStorms
Nov 26 '18 at 12:36

add a comment |

You could perhaps RIGHT JOIN to a sub-query with the 12 months. Then group on the month from the sub-query.

– LukStorms
Nov 26 '18 at 12:36

You could perhaps RIGHT JOIN to a sub-query with the 12 months. Then group on the month from the sub-query.

– LukStorms
Nov 26 '18 at 12:36

add a comment |

1 Answer
1

active

oldest

votes

try IFNULL :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                 MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                 `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                 SUM(IFNULL(amount_after_fees, 0)) as total FROM `transaction`

WHERE `record_owner_user_id` = '50' 

                     AND `is_one_time_purchase` = 'Y' 

                     AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                     and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` 

ORDER BY `id` ASC

also try COALESCE :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                     MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                     `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                     COALESCE(SUM(amount_after_fees),0) as total FROM `transaction`

    WHERE `record_owner_user_id` = '50' 

                         AND `is_one_time_purchase` = 'Y' 

                         AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                         and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

    GROUP BY `month` 

    ORDER BY `id` ASC

edited Nov 26 '18 at 12:22

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

What happen if last month have no record, then in group by it will not take that month so it will not give me record

– Nikul Panchal
Nov 26 '18 at 12:22

it will return 0

– Saurabh Mistry
Nov 26 '18 at 12:24

@NikulPanchal , you can try this :stackoverflow.com/questions/32284341/…

– Saurabh Mistry
Nov 26 '18 at 12:27

This doesn't work if the table contains no transactions in a particular month, unfortunately.

– O. Jones
Nov 26 '18 at 12:29

add a comment |

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

try IFNULL :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                 MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                 `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                 SUM(IFNULL(amount_after_fees, 0)) as total FROM `transaction`

WHERE `record_owner_user_id` = '50' 

                     AND `is_one_time_purchase` = 'Y' 

                     AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                     and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` 

ORDER BY `id` ASC

also try COALESCE :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                     MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                     `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                     COALESCE(SUM(amount_after_fees),0) as total FROM `transaction`

    WHERE `record_owner_user_id` = '50' 

                         AND `is_one_time_purchase` = 'Y' 

                         AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                         and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

    GROUP BY `month` 

    ORDER BY `id` ASC

edited Nov 26 '18 at 12:22

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

What happen if last month have no record, then in group by it will not take that month so it will not give me record

– Nikul Panchal
Nov 26 '18 at 12:22

it will return 0

– Saurabh Mistry
Nov 26 '18 at 12:24

@NikulPanchal , you can try this :stackoverflow.com/questions/32284341/…

– Saurabh Mistry
Nov 26 '18 at 12:27

This doesn't work if the table contains no transactions in a particular month, unfortunately.

– O. Jones
Nov 26 '18 at 12:29

add a comment |

try IFNULL :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                 MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                 `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                 SUM(IFNULL(amount_after_fees, 0)) as total FROM `transaction`

WHERE `record_owner_user_id` = '50' 

                     AND `is_one_time_purchase` = 'Y' 

                     AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                     and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` 

ORDER BY `id` ASC

also try COALESCE :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                     MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                     `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                     COALESCE(SUM(amount_after_fees),0) as total FROM `transaction`

    WHERE `record_owner_user_id` = '50' 

                         AND `is_one_time_purchase` = 'Y' 

                         AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                         and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

    GROUP BY `month` 

    ORDER BY `id` ASC

edited Nov 26 '18 at 12:22

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

What happen if last month have no record, then in group by it will not take that month so it will not give me record

– Nikul Panchal
Nov 26 '18 at 12:22

it will return 0

– Saurabh Mistry
Nov 26 '18 at 12:24

@NikulPanchal , you can try this :stackoverflow.com/questions/32284341/…

– Saurabh Mistry
Nov 26 '18 at 12:27

This doesn't work if the table contains no transactions in a particular month, unfortunately.

– O. Jones
Nov 26 '18 at 12:29

add a comment |

try IFNULL :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                 MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                 `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                 SUM(IFNULL(amount_after_fees, 0)) as total FROM `transaction`

WHERE `record_owner_user_id` = '50' 

                     AND `is_one_time_purchase` = 'Y' 

                     AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                     and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` 

ORDER BY `id` ASC

also try COALESCE :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                     MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                     `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                     COALESCE(SUM(amount_after_fees),0) as total FROM `transaction`

    WHERE `record_owner_user_id` = '50' 

                         AND `is_one_time_purchase` = 'Y' 

                         AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                         and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

    GROUP BY `month` 

    ORDER BY `id` ASC

edited Nov 26 '18 at 12:22

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

try IFNULL :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                 MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                 `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                 SUM(IFNULL(amount_after_fees, 0)) as total FROM `transaction`

WHERE `record_owner_user_id` = '50' 

                     AND `is_one_time_purchase` = 'Y' 

                     AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                     and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

GROUP BY `month` 

ORDER BY `id` ASC

also try COALESCE :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,

                     MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,

                     `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 

                     COALESCE(SUM(amount_after_fees),0) as total FROM `transaction`

    WHERE `record_owner_user_id` = '50' 

                         AND `is_one_time_purchase` = 'Y' 

                         AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 

                         and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)

    GROUP BY `month` 

    ORDER BY `id` ASC

edited Nov 26 '18 at 12:22

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

edited Nov 26 '18 at 12:22

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

answered Nov 26 '18 at 12:19

Saurabh Mistry

4,21511030

What happen if last month have no record, then in group by it will not take that month so it will not give me record

– Nikul Panchal
Nov 26 '18 at 12:22

it will return 0

– Saurabh Mistry
Nov 26 '18 at 12:24

@NikulPanchal , you can try this :stackoverflow.com/questions/32284341/…

– Saurabh Mistry
Nov 26 '18 at 12:27

This doesn't work if the table contains no transactions in a particular month, unfortunately.

– O. Jones
Nov 26 '18 at 12:29

add a comment |

What happen if last month have no record, then in group by it will not take that month so it will not give me record

– Nikul Panchal
Nov 26 '18 at 12:22

it will return 0

– Saurabh Mistry
Nov 26 '18 at 12:24

@NikulPanchal , you can try this :stackoverflow.com/questions/32284341/…

– Saurabh Mistry
Nov 26 '18 at 12:27

This doesn't work if the table contains no transactions in a particular month, unfortunately.

– O. Jones
Nov 26 '18 at 12:29

What happen if last month have no record, then in group by it will not take that month so it will not give me record

– Nikul Panchal
Nov 26 '18 at 12:22

it will return 0

– Saurabh Mistry
Nov 26 '18 at 12:24

@NikulPanchal , you can try this :stackoverflow.com/questions/32284341/…

– Saurabh Mistry
Nov 26 '18 at 12:27

This doesn't work if the table contains no transactions in a particular month, unfortunately.

– O. Jones
Nov 26 '18 at 12:29

add a comment |

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytukyg