On Friday 07 Feb 2003 12:57 pm, Gary Stainburn wrote: > > Thinking about it, I'm not wanting to perform a join as such, but a merge > of the two selects below, then some form of group by to sum() the two > counts. > > select rosid as sid, rojid as jid, count(*) as count > from roster group by sid, jid order by sid, jid; > select hsid as sid, hjid as jid, hcount as count > from history order by sid, jid; > > so that > > 1 2 1 > 1 3 2 > > and > > 1 3 1 > 1 4 2 > > becomes > > 1 2 1 > 1 3 3 > 1 4 2
You want a UNION, something like (untested): CREATE VIEW all_counts AS SELECT rosid as sid, rojid as jid, count(*) as all_count FROM roster GROUP BY sid,jid UNION ALL SELECT hsid,hjid,hcount FROM history; SELECT sid,jid,SUM(all_count) FROM all_counts GROUP BY sid,jid; -- Richard Huxton ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
