Re: NewBie : SQL Problem..

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Sun, 31 May 1998 00:05:44 GMT
Message-ID: <35709e19.5395953@www.sigov.si>

On Fri, 29 May 1998 13:36:48 GMT, ashesh48_at_my-dejanews.com wrote:

>Hi,
> I have a problem with counts.
> I have 3 tables.. wtiht the following no.of rows in it..
> table name count(rows)
> ------------------- ------------------
> A 25
> B 5
> C 10
>
>Now i want to create another table (D) which can have the following coulumns..
> Columns
> 1. Table name
> 2. Counts.
>
>Out put.. sholud be as follow...
> table name counts.
> ---------------- ----------
> A 25
> B 5
> C 10
> I am not able to get the query.. for that. Please help...

Use the UNION operator:

CREATE TABLE d (table_name VARCHAR2(30), counts NUMBER) AS

  SELECT 'A', COUNT(*) FROM a UNION ALL
  SELECT 'B', COUNT(*) FROM b UNION ALL
  SELECT 'C', COUNT(*) FROM c;

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Sat May 30 1998 - 19:05:44 CDT