MYSQL: Add some data in the first row only based on group by ID

Question

I have this set of sample data:

Table invoice:

--------------------------------------------------
| ID |     date     | invoice_number |   total   |
--------------------------------------------------
| 81 |  2017-03-24  |   0000000173   |  190.00   |
--------------------------------------------------

Table invoice_addon:

----------------------------------------------------------
| ID |  invoice_id  |       description       |  amount  |
----------------------------------------------------------
| 46 |      81      | Price Adjust. - Jumbo   |  -12.00  |
| 47 |      81      | Price Adjust. - Regular |  -12.00  |
----------------------------------------------------------

Table orders:

----------------------------------------------------------------------------------
| ID  |  invoice_id  |  box_name   |   size   |  price  |   tax   |  box_number  |
----------------------------------------------------------------------------------
| 177 |      81      | Jumbo Box   | 23x25x17 |  97.00  |  15.00  |  FCI107056   |
| 178 |      81      | Regular Box | 20x23x17 |  87.00  |  15.00  |  FCI107057   |
----------------------------------------------------------------------------------

What I want to achieve:

----------------------------------------------------------------------------------------------------------------------------------
| trans_date  | inv_number |  box_name   |   size   |  gross   |  box_number  | others | description                     |  net  |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Jumbo Box   | 23x25x17 |  112.00  |  FCI107056   | -24.00 |  Price Adjust. - Jumbo -12.00   | 88.00 |
|             |            |             |          |          |              |        |  Price Adjust. - Regular -12.00 |       |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Regular Box | 20x23x17 |  102.00  |  FCI107057   |      0 |  NULL                           | 102.00|
----------------------------------------------------------------------------------------------------------------------------------

My current query:

SELECT DATE(i.date) AS trans_date, i.invoice_number AS inv_number, o.box_name as box_name, 
o.size AS size, (SELECT Price + Tax FROM orders WHERE ID = o.ID) AS gross, 
o.box_number AS box_number,
SUM(a.amount) AS others, 
(SELECT GROUP_CONCAT(CONCAT(description, ' ', amount) SEPARATOR '<br />') FROM invoice_addon WHERE invoice_id = i.ID) AS description, 
(SUM(o.price + o.tax) + SUM(a.amount)) AS net 
FROM `invoice` i 
INNER JOIN orders o ON i.ID = o.invoice_id 
LEFT JOIN invoice_addon a ON i.ID = a.invoice_id 
WHERE i.ID = 81
GROUP BY o.ID

The problem in the result in my query is, others and description columns are doubled. It should only be shown in the 1st row. No matter how many boxes are tied up in the invoice, they should only be added in the 1st row. The net also relies on those columns.

What I get:

----------------------------------------------------------------------------------------------------------------------------------
| trans_date  | inv_number |  box_name   |   size   |  gross   |  box_number  | others | description                     |  net   |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Jumbo Box   | 23x25x17 |  112.00  |  FCI107056   | -24.00 |  Price Adjust. - Jumbo -12.00   | 200.00 |
|             |            |             |          |          |              |        |  Price Adjust. - Regular -12.00 |        |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24  | 0000000173 | Regular Box | 20x23x17 |  102.00  |  FCI107057   | -24.00 |  Price Adjust. - Jumbo -12.00   | 180.00 |
|             |            |             |          |          |              |        |  Price Adjust. - Regular -12.00 |        |
----------------------------------------------------------------------------------------------------------------------------------

Is it possible? How can I make it so? (I'm doing this in MySQL using CodeIgniter and DataTable)

Answer 1

Well, you need some var that can store current row number and check if it equal to 1. Something like this:

SET @row_number = 0;
SELECT DATE(i.date) AS trans_date, i.invoice_number AS inv_number, o.box_name as box_name, 
o.size AS size, (SELECT Price + Tax FROM orders WHERE ID = o.ID) AS gross, 
o.box_number AS box_number,
if((@row_number:=@row_number + 1)=1,SUM(a.amount), null) AS others, 
if(@row_number=1,(SELECT GROUP_CONCAT(CONCAT(description, ' ', amount) 
SEPARATOR '<br />') FROM invoice_addon WHERE invoice_id = i.ID), null) AS description, 
(SUM(o.price + o.tax) + SUM(a.amount)) AS net 
FROM `invoice` i 
INNER JOIN orders o ON i.ID = o.invoice_id 
LEFT JOIN invoice_addon a ON i.ID = a.invoice_id  
WHERE i.ID = 81
GROUP BY o.ID