Display the result in table By month

Question

I am trying to get the total of my intax and outTax. I have the right query, if i run it shows proper result. But i am not able to display it as i need.

Here is my code.

  $sqlOut = "SELECT sales_invoice.invoice_id, MONTHNAME(sales_invoice.date_invoiced) AS month, sales_invoice_line_items.invoice_id, sales_invoice_line_items.tax, SUM(sales_invoice_line_items.tax_amount) AS totaltax, taxes.tax_id, taxes.rate, taxes.name AS Tname FROM sales_invoice INNER JOIN sales_invoice_line_items ON sales_invoice.invoice_id=sales_invoice_line_items.invoice_id INNER JOIN taxes ON sales_invoice_line_items.tax=taxes.tax_id WHERE sales_invoice_line_items.tax=".$tax." GROUP BY sales_invoice.date_invoiced";

$sqlIn = "SELECT purchase_invoice.invoice_id, MONTHNAME(purchase_invoice.date_invoiced) AS month, purchase_invoice_line_items.invoice_id, purchase_invoice_line_items.tax, SUM(purchase_invoice_line_items.tax_amount) AS totaltax, taxes.tax_id, taxes.rate, taxes.name AS Tname FROM purchase_invoice INNER JOIN purchase_invoice_line_items ON purchase_invoice.invoice_id=purchase_invoice_line_items.invoice_id INNER JOIN taxes ON purchase_invoice_line_items.tax=taxes.tax_id WHERE purchase_invoice_line_items.tax=".$tax." GROUP BY purchase_invoice.date_invoiced";

$ResOut = mysql_query($sqlOut) or die(mysql_error());

$ResIn = mysql_query($sqlIn) or die(mysql_error());

}

I want to display it like this

<td>Months</td><td>Out Tax</td><td>In Tax</td><td>Difference(OutTax-InTax)</td>

My output format has to be

<table><tr>
<td>Months</td><td>Out Tax</td><td>In Tax</td><td>Difference(OutTax-InTax)</td></tr>
<tr><td>Jan</td>   <td>3456</td>    <td>2311</td>   <td>1145</td></tr>
<tr><td>March</td> <td>4123</td>    <td>3125</td>   <td>2978</td></tr>
</table>

And table structure is purchase_invoice table

CREATE TABLE IF NOT EXISTS `purchase_invoice` (
  `invoice_id` int(50) NOT NULL AUTO_INCREMENT,
  `order_id` int(50) NOT NULL,
  `date_invoiced` date NOT NULL,
  `status` varchar(10) NOT NULL,
  PRIMARY KEY (`invoice_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

purchase_invoice_line_items

CREATE TABLE IF NOT EXISTS `purchase_invoice_line_items` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(50) NOT NULL,
  `tax` int(10) NOT NULL,
  `discount` int(10) NOT NULL,
  `freight` int(20) NOT NULL,
  `sub_total` double NOT NULL,
  `tax_amount` double NOT NULL,
  `reason` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Can somebody please help me in this

what is your table structure? and what is your expected output with sample data? — Vishal Zanzrukia, Jan 05 '15 at 07:32
You are grouping by date. In order to display the data per month, you have to group by month and year... — Andreas Aumayr, Jan 05 '15 at 07:35

Saharsh Shah · Accepted Answer · 2015-01-05T09:35:28.747

Try this :

SELECT A.month_invoiced AS 'Months', A.totaltax AS 'Out Tax', 
         B.totaltax AS 'In Tax', (A.totaltax - B.totaltax) AS 'Difference(OutTax-InTax)'
FROM (SELECT MONTHNAME(si.date_invoiced) AS month_invoiced, 
             EXTRACT(YEAR_MONTH FROM si.date_invoiced) AS YM_Invoced, 
             SUM(sil.tax_amount) AS totaltax
      FROM sales_invoice si 
      INNER JOIN sales_invoice_line_items  sil ON si.invoice_id=sil.invoice_id 
      INNER JOIN taxes t ON sil.tax=t.tax_id 
      WHERE sil.tax=".$tax." 
      GROUP BY YM_Invoced
     ) AS A 
INNER JOIN (SELECT MONTHNAME(pi.date_invoiced) AS month_invoiced, 
                   EXTRACT(YEAR_MONTH FROM pi.date_invoiced) AS YM_Invoced, 
                   SUM(pil.tax_amount) AS totaltax
            FROM purchase_invoice PI 
            INNER JOIN purchase_invoice_line_items pil ON pi.invoice_id=pil.invoice_id 
            INNER JOIN taxes t ON pil.tax=t.tax_id 
            WHERE pil.tax=".$tax." 
            GROUP BY YM_Invoced
          ) AS B ON A.YM_Invoced = B.YM_Invoced;

::EDIT::

SELECT A.month_invoiced, 
         MAX(CASE WHEN A.taxType = 'Out' THEN A.totaltax ELSE 0 END) AS 'Out Tax', 
         MAX(CASE WHEN A.taxType = 'In' THEN A.totaltax ELSE 0 END) AS 'In Tax', 
        (IFNULL(MAX(CASE WHEN A.taxType = 'Out' THEN A.totaltax ELSE 0 END), 0) - 
         IFNULL(MAX(CASE WHEN A.taxType = 'In' THEN A.totaltax ELSE 0 END), 0)
        ) AS 'Difference(OutTax-InTax)' 
FROM (SELECT MONTHNAME(si.date_invoiced) AS month_invoiced, 
                 EXTRACT(YEAR_MONTH FROM si.date_invoiced) AS YM_Invoced, 
                 SUM(sil.tax_amount) AS totaltax, 
                 'Out' AS taxType
        FROM sales_invoice si 
        INNER JOIN sales_invoice_line_items  sil ON si.invoice_id=sil.invoice_id 
        INNER JOIN taxes t ON sil.tax=t.tax_id 
        WHERE sil.tax=".$tax." 
        GROUP BY YM_Invoced
        UNION 
        SELECT MONTHNAME(pi.date_invoiced) AS month_invoiced, 
                 EXTRACT(YEAR_MONTH FROM pi.date_invoiced) AS YM_Invoced, 
                 SUM(pil.tax_amount) AS totaltax, 
                 'IN' AS taxType
        FROM purchase_invoice PI 
        INNER JOIN purchase_invoice_line_items pil ON pi.invoice_id=pil.invoice_id 
        INNER JOIN taxes t ON pil.tax=t.tax_id 
        WHERE pil.tax=".$tax." 
        GROUP BY YM_Invoced
     ) AS A 
GROUP BY A.YM_Invoced

Yes, but if there is not a intax for the month of March and but has outtax for march, then its not showing the data. I tried left join for same query, it didnt work — user2823107, Jan 05 '15 at 07:54
If i user RIGHT JOIN, this works, but when i do not have data in my LEFT table RIGHT jOIN wont work. I am getting error if i use union or union all — user2823107, Jan 05 '15 at 08:35
This works properly!! Thank you. Just have to remove `,` in `AS 'Out Tax', FROM`. Thank you very much — user2823107, Jan 05 '15 at 09:27

Display the result in table By month

1 Answers1