Combining 2 data fields in to one

[dcjones]

Hi all,

I am trying to add together the content of fields in my table, like this:

SELECT master_ecn_t1,
master_ecn_t2,
master_ecn_t3,
SUM(final_sales_value_t2 + final_sales_value_t3),    

SUM(final_vat_value_t2 + final_vat_value_t3),   

SUM(final_net_value_t2 + final_net_value_t3),   

SUM(commission_due_t2 + commission_due_t3),
paid
FROM sales_commission
WHERE master_ecn_t1 = 4ecn
GROUP BY tier

My question, is it possible to combine "master_ecn_t2" and "master_ecn_t3" fields into one field so that the result is like this;

master ecn data | final_sales_value_t2 | final_vat_value_t2 | final_net_value_t2 | commission_due_t2

[NogDog]

You don't say what DBMS, but in MySQL you can use the CONCAT() or CONCAT_WS() function. Other DBMS's will have similar functions/operators.

[dcjones]

Hi NogDog,

Thanks for your reply.

I think I am getting my head around this. So far I have the following:

SELECT master_ecn_t1, 
GROUP_CONCAT(master_ecn_t2,',',master_ecn_t3), 
SUM(final_sales_value_t2 + final_sales_value_t3),     

SUM(final_vat_value_t2 + final_vat_value_t3),    

SUM(final_net_value_t2 + final_net_value_t3),    

SUM(commission_due_t2 + commission_due_t3), 
paid 
FROM sales_commission 
WHERE master_ecn_t1 = 4ecn 
GROUP BY tier 

To display the result of the GROUP_CONCAT I have

echo $row_promotion_ecn_summary['GROUP_CONCAT(master_ecn_t2,',',master_ecn_t3)'];

When I runs the script I am getting the following error:

Parse error: syntax error, unexpected ',', expecting ']'

What I am trying to do is to display the content of the GROUP_CONCAT with the line break "<br /> betwen each data part.

Can you see where I am goig wrong.

Thanks for you help yet again.

<?php ?>

[NogDog]

I would use an alias:

. . . GROUP_CONCAT(foo, bar) AS `combined` . . .

Then you can just refer to it as $row_promotion_ecn_summary['combined'] (or whatever alias name makes more sense to you than "combined").

[dcjones]

Hi NogDog,

I have this working but I think I am looking at this problem incorrectly.

I have my combined field with the following content:

6C3CB79337,BA4DD8287B

What I am trying to do is to display the content on two rows like:

6C3CB79337
BA4DD8287B

so the complete rows looks like:

6C3CB79337 | 2 | 9.98 | 2.60 | 17.38
BA4DD8287B | 1 | 9.99 | 1.30 | 8.69

Any ideas as to how I can do this.

once again, thanks for your help