NewBie : SQL Problem..

NewBie : SQL Problem..

Post by Jurij Mod » Mon, 01 Jun 1998 04:00:00




>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

============================================================
The above opinions are mine and do not represent any official
standpoints of my employer

 
 
 

1. Is there a better way? Newbie SQL Problem

Hello,

listed below is the answer to a SQL problem I have.  I cant help but
thinking however that I took the long way to get to the solution.

Being somewhat of a newbie at this stuff I was hoping that someone could
enlighten me of an easier way to accomplish my goal without using temporary
tables.

The problem is this:

Every vendor_invoice_number must have its own voucher_number.  Duplicate
invoice_number + voucher_numbers are okay.  What is not okay is 2 different
voucher_numbers with the same invoice_numbers.

Any insight is greatly appreciated.

Brad.

drop table dupl_voucher_invoice
go
CREATE TABLE [dupl_voucher_invoice] (
 [voucher_num] [char] (10) NOT NULL ,
 [vendor_inv_num] [int] NOT NULL
) ON [PRIMARY]
go
insert into dupl_voucher_invoice
values('ut10027', 1240)
insert into dupl_voucher_invoice
values('ut10012', 1235)
insert into dupl_voucher_invoice
values('ut10013', 1234) --this is an error
insert into dupl_voucher_invoice
values('ut10011', 1234)
insert into dupl_voucher_invoice
values('ut10021', 1239)
insert into dupl_voucher_invoice
values('ut10015', 1234)
insert into dupl_voucher_invoice
values('ut10019', 1239)
insert into dupl_voucher_invoice
values('ut10033', 1250)
insert into dupl_voucher_invoice
values('ut10033', 1250)
insert into dupl_voucher_invoice
values('ut10044', 1260)
insert into dupl_voucher_invoice
values('ut10045', 1260)
insert into dupl_voucher_invoice
values('ut100101', 1295)
insert into dupl_voucher_invoice
values('ut100101', 1295)
insert into dupl_voucher_invoice
values('ut100100', 1290)
insert into dupl_voucher_invoice
values('ut100101', 1290)
insert into dupl_voucher_invoice
values('ut10033', 1250)
insert into dupl_voucher_invoice
values('ut100115', 1297)
insert into dupl_voucher_invoice
values('ut100115', 1297)
go
select distinct(voucher_num),vendor_inv_num
INTO ##vendor_invoice_check
from dupl_voucher_invoice
go
select vendor_inv_num, count(voucher_num) vouchernum_cnt
INTO ##vendor_invoice_count
from ##Vendor_Invoice_check
group by vendor_inv_num
having count(voucher_num) > 1
go
select distinct(a.vendor_inv_num)
from ##vendor_invoice_check a, ##vendor_invoice_count b
where a.vendor_inv_num = b.vendor_inv_num
go
drop table ##vendor_invoice_count
drop table ##vendor_invoice_check
go

2. add table

3. Newbie SQL Problem

4. Help with select (simple I hope)

5. Newbie SQL problems

6. FlexControl Lookup

7. SQL, VB, Access, Queries, Join Problem, and Newbie Problem

8. Corrupted SQL scripts

9. SQL Newbie - Problems with loading data from access to sql database

10. Newbie has SQL problem - please help

11. Newbie, w/ SQL Server Authentication problems

12. Problem running stored procedure by SQL Server Newbie

13. newbie: sql server 7 query problem