Need help creating a complicated mysql query.

Hello all, thanks in advance!!! I am sorry if this is not in the correct category...

Here is my mysql table:

+-----+-----+------+-----+
| id | tid | tid2 | amt |
+-----+-----+------+-----+
| 327 | 114 | 0 | 33 |
| 341 | 114 | 115 | 44 |
| 342 | 118 | 0 | 33 |
| 343 | 115 | 114 | 33 |
| 344 | 115 | 120 | 33 |
+-----+-----+------+-----+

the tid and tid2 are similar but the first one is primary tid and the second is secondary... They're like a primary category and a secondary category.

I am trying to pull out a sum(amt) for each tid (tid and tid2). I would like them displayed having all the tids grouped together.

If you look at line 1 and 2, the tid is 114 but then line 4 tid2 is also 114, so I want all 3 line's amt summed together... I keep having a problem getting them to sum.

Here is my query:

select * from
select tid as ti, sum(amt) from mytable group by ti
UNION
select tid2 as ti, sum(amt) from mytable where tid2 != 0 group by ti
) as am GROUP BY ti;

this gives me:

+-----+----------+
| ti | sum(amt) |
+-----+----------+
| 114 | 77 |
| 115 | 66 |
| 118 | 33 |
| 120 | 33 |
+-----+----------+

it groups them nicely but is not summing all the amts, looks like it's giving me the amt of one of the union sub queries.

Here is what I would like to receive:

+-----+----------+
| ti | sum(amt) |
+-----+----------+
| 114 | 110 | <- that should sum up the amt for all the tid = 114 and tid2 = 114
| 115 | 110 | <- that should sum up the amt for all the tid = 115 and tid2 = 115
| 118 | 33 | <- that should sum up the amt for all the tid = 118 and tid2 = 118
| 120 | 33 | <- that should sum up the amt for all the tid = 120 and tid2 = 120
+-----+----------+

Please help me out. Thank you so much!!!

~ V

 

This works in MS SQL Server, I cannot test it in MySQL. The principes should be the same.

select tid,sum(amt) as sumAmt
from(
	select id, tid, amt
		from mytable as t1
	union all
	select ID, tid2, amt
		from mytable as t1
) as der
group by tid
having tid > 0

Code (markup):

tid sumAmt
----------- -----------
114 110
115 110
118 33
120 33

(4 row(s) affected)

 

Yeah, it's not working here... giving me an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as t1 UNION ALL select id, tid2, amt from mytable at line 2

 

Ok figured it out:

select tid, sum(amt) as sumAmt from
(select id, tid, amt from mytable
UNION ALL
select id, tid2, amt from mytable where tid2 != '0')
as am GROUP BY tid;


This gives me:


+-----+--------+
| tid | sumAmt |
+-----+--------+
| 114 | 110 |
| 115 | 110 |
| 118 | 33 |
| 120 | 33 |
+-----+--------+

Perfecto!! Thanks so much!