sum a set of records/numbers in mysql

Forum Moderators: open

Message Too Old, No Replies

sum a set of records/numbers in mysql

kirang

2:09 pm on May 2, 2008 (gmt 0)

Hello,

I had table records as :

+-------+--------+--------+
Śurlid1 Ś urlid2 ŚjobcountŚ
+-------+--------+--------+
Ś129147 Ś130676 Ś 305 Ś
Ś130676 Ś129147 Ś 72 Ś
Ś130676 Ś129149 Ś 756 Ś
Ś129149 Ś130676 Ś 30 Ś
+-------+--------+--------+

Now, i need to club the records, where urlid1 = urlid2 and urlid2 = urlid1 and take the job count for those records.

Means that 1st record of urlid1(129147) should search the column urlid2 and also vice versa and finally it should sum the jobcount column.

Expected output:
+-------+--------+--------+
Śurlid1 Ś urlid2 ŚjobcountŚ
+-------+--------+--------+
Ś129147 Ś130676 Ś 377 Ś
Ś130676 Ś129149 Ś 786 Ś
+-------+--------+--------+

I tried several combinations but all in vain.

SELECT a.urlid1, sum( a.jobcount )
FROM test AS a
JOIN test AS b ON a.urlid1 = b.urlid2
AND a.urlid2 = b.urlid1
GROUP BY a.urlid1

any ideas would be great help.

Thanks

ashish21cool

4:38 am on May 3, 2008 (gmt 0)

Hi,

You cannot join within 1 table.

Let me know the exact structure of you table.

Thanks

kirang

5:51 am on May 3, 2008 (gmt 0)

Hi,

Table structure of table is :

Table name test
1) urlid1 int(2)
2) urlid2 int(2)
3) jobcount int(2)

ashish21cool

9:13 am on May 3, 2008 (gmt 0)

According to me you are looking out for this:
You want to sum all jobcount for say urlid1=x and urlid2=x
If above is the case then do the following:
SELECT ulrid1, urlid2,
(SELECT SUM(jobcount) AS jobcount
FROM tp
WHERE (ulrid1 = 129147) OR
(urlid2 = 129147)) AS jobcount
FROM tp AS tp_1
WHERE (ulrid1 = 129147)

This will solve you problem.

Let me know if you get it or not.

All the best