Here's the situation:
I have (2) tables, one is the ticketitem table, and the other is a payment table.
The ticketitem table can have multiple tickets with the same id. Ex. "ticketid=1".
I'm trying to use the MYSQL sum() function to add the amount column and post the amount to the payment table. If there's a single ticketid record, the sum() function works fine. If there are multiple ticketid records, the sum() function returns "0". I've been struggling with this for hours and don't know why it does not work. I've tried both 3.22 and 3.23 release and it yielded the same result. My insert command is as follows:
"insert into payment (customerid, ticketid, paymentdate, paymenttype, amount) select customerid, ticketid, now(), payid, sum(amount) as total from ticketitemtemp group by ticketid"
I have a way around this, but I don't understand why this does not work. Any recommendation will be greatly appreciated.
TIA, Allen
I have (2) tables, one is the ticketitem table, and the other is a payment table.
The ticketitem table can have multiple tickets with the same id. Ex. "ticketid=1".
I'm trying to use the MYSQL sum() function to add the amount column and post the amount to the payment table. If there's a single ticketid record, the sum() function works fine. If there are multiple ticketid records, the sum() function returns "0". I've been struggling with this for hours and don't know why it does not work. I've tried both 3.22 and 3.23 release and it yielded the same result. My insert command is as follows:
"insert into payment (customerid, ticketid, paymentdate, paymenttype, amount) select customerid, ticketid, now(), payid, sum(amount) as total from ticketitemtemp group by ticketid"
I have a way around this, but I don't understand why this does not work. Any recommendation will be greatly appreciated.
TIA, Allen