Käynnissä

Order query by month although there record exists

I have the following query that give me paid, unpaid and total from a default period (actual month to 9 month ago). At this point all works fine.

My problem is when some periods have not results (I made GROUP BY months). For this particular records I need that return a 0 value for paid, unapid and total, and not ignore, because then I show this in a html table ordering by month and empty values produce that some columns not appear.

This is my query at this moment:

SELECT

YEAR(PI.date_invoice) AS year,

MONTH(PI.date_invoice) AS month,

@unpaid := SUM(CASE

WHEN [url removed, login to view] = '1' THEN PI.amount_dolar

ELSE 0

END) AS unpaid,

@paid := SUM(CASE

WHEN [url removed, login to view] = '2' THEN PI.amount_dolar

ELSE 0

END) AS paid,

@total := SUM(CASE

WHEN [url removed, login to view] != '3' THEN PI.amount_dolar

ELSE 0

END) AS total

FROM crm_provider_invoice PI

LEFT JOIN crm_provider P ON(PI.id_provider = P.id_provider)

WHERE DATE_FORMAT(PI.date_invoice, '%Y-%m-%d') BETWEEN DATE_SUB(LAST_DAY(SYSDATE()), INTERVAL 9 MONTH) AND DATE_SUB(LAST_DAY(SYSDATE()), INTERVAL 0 MONTH)

GROUP BY MONTH(PI.date_invoice)

ORDER BY year DESC, month DESC

What I need are records, month by month, with o without results. And in case that no result for one month, return with zero value.

Taidot: MySQL, PHP

Näytä lisää: n query, group o, query table, desc, php mysql join table, html order total, mysql join table, mysql table join, table month php, html order, works join, record show, sysdate mysql, order status pending, mysql join, end month, pending order status, order mysql, php mysql table join, total order, query year php, order status mysql, select table html, sum total, ignore mysql

About the Employer:
( 1 review ) Olivos, Argentina

Projektin tunnus: #1685815

2 freelanceria on tarjonnut keskimäärin 7 $/tunti tähän työhön

chetanraj81lx

I can fix your issue. Check pmb

$5 USD / tunti
(13 arvostelua)
5.3
manhpham

I am willing to do this project. Please contact me asap.

$8 USD / tunti
(13 arvostelua)
3.8