Home » SQL & PL/SQL » SQL & PL/SQL » Aggregate Count From Two Tables
Aggregate Count From Two Tables [message #266755] Tue, 11 September 2007 09:52 Go to next message
deay
Messages: 51
Registered: August 2005
Member
Hi All

I need to combine two counts from different tables for the same 'MMYYYY' (see below).

I tried using a UNION in an attempt to combine the two totals
for '022007' that should equal 113940.

  1  select to_char(r.acknowledged_date,'MMYYYY') as project,count(r.receipt) as Dropped
  2  from payment r
  3  where to_char(r.acknowledged_date,'YYYY')='2007'
  4  and r.receipt='Y'
  5  group by to_char(r.acknowledged_date,'MMYYYY')
  6  UNION
  7  select to_char(e.ack_date,'MMYYYY'),e.receipt_count
  8* from eoy_tku e
SQL> /

PROJEC    DROPPED
------ ----------
012007      33716
022007      30618
022007      83322
032007      41123
042007      28748
052007      37812
062007      30636
072007      27009
082007      30058
092007       6262

10 rows selected.

SQL> select * from eoy_tku;

ACK_DATE  RECEIPT_COUNT DEF_
--------- ------------- ----
25-FEB-07         83322 07BY


any tips on how to get the February totals combined is greatly appreciated.

thanks
Re: Aggregate Count From Two Tables [message #266757 is a reply to message #266755] Tue, 11 September 2007 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>combine the two totals
Most folks would use simple addition to get the total.
Re: Aggregate Count From Two Tables [message #266759 is a reply to message #266755] Tue, 11 September 2007 10:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try
SELECT project, sum(dropped)
FROM (<your query>)
GROUP BY project;
Re: Aggregate Count From Two Tables [message #266762 is a reply to message #266759] Tue, 11 September 2007 10:16 Go to previous message
deay
Messages: 51
Registered: August 2005
Member
PERFECT! THANKS! JRowBottom
my dilemma was with trying to get the two dates from the separate
tables to group together.

SQL> select project, sum(dropped)
  2  from(select to_char(r.acknowledged_date,'MMYYYY') as project,count(r.receipt) as Dropped
  3  from payment r
  4  where to_char(r.acknowledged_date,'YYYY')='2007'
  5  and r.receipt='Y'
  6  group by to_char(r.acknowledged_date,'MMYYYY')
  7  UNION ALL
  8  select to_char(e.ack_date,'MMYYYY') as project,e.receipt_count as Dropped
  9  from eoy_tku e)
 10  group by project;

PROJEC SUM(DROPPED)
------ ------------
012007        33716
022007       113940
032007        41123
042007        28748
052007        37812
062007        30636
072007        27009
082007        30058
092007         6262

9 rows selected.
Previous Topic: With after cursor name
Next Topic: Identify non-numeric
Goto Forum:
  


Current Time: Fri Dec 09 08:08:18 CST 2016

Total time taken to generate the page: 0.22732 seconds