issue with the union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue with the union

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 12:09:24

I have a union issue and display issue..Here I have to get the count from each table.The first - repay as table_name and count(*) as pay_count is fine

case 1
but the next is quote_count is actually a count of pay id which is present in tbl_repayment and tbl_quote

and is the same as TBS_count is a count of payid which is present in tbl_repayment and tbl_tbs

case 2

The union here that is the second query quote_count is actually a count of pay id which is present in tbl_payment and tbl_quote

and is the same as TBS_count is a count of payid which is present in tbl_payment and tbl_tbs

please help me get the count

SELECT 'Repay' AS table_name,COUNT(*) AS pay_count ,'quote' as quote_count ,'TPS' as TBS_count FROM tbl_repayment
UNION ALL
SELECT 'payment' AS table_name,COUNT(*) AS pay_count ,'quote' as quote_count ,'TBS' as TBS_count FROM tbl_payment

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 12:11:33

i don't get it.

how about your table structure and some sample data, and based on that sample data, what results you are looking for? You know the drill by now, right?

- Jeff

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 12:27:17

Let me show the result what iam looking at..........

Module Count quote_count TBS_count Offer_count

Repay 65 14 3 50
payment 336 4 3 70

Here the the first countis the count of the table tbl_repay,
The second quote_count is the count of the payid present in tbl_repay and tbl_quote
3rd TBS_count is the count of the payid present in tbl_repay and tbl_tbs
4rth Offer_count is the count of the payid present in tbl_repay and tbl_offer

union

The first countis the count of the table tbl_payment,
The second quote_count is the count of the payid present in tbl_payment and tbl_quote
3rd TBS_count is the count of the payid present in tbl_payment and tbl_tbs
4rth Offer_count is the count of the payid present in tbl_payment and tbl_offer

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 13:26:34

Please help me with this query

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 13:32:45

how about your table structure and some sample data, and based on that sample data, what results you are looking for? You know the drill by now, right?

- Jeff

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 13:46:01

Hope this will help in more.....

tbl_repayment
-------------

pay_id initial_deposit due_date
112 200 12/12/2004
113 300 10/10/2004
114 100 09/09/2004
115 50 12/10/2004
116 233 11/12/1004
117 33 09/10/2004

tbl_payment
------------

pay_id payed_date payed_amount
112 10/11/2004 500
113 09/09/2004 200
114 12/10/2004 100
115 09/10/2004 400
116 12/12/2004 600
117 10/10/2004 700

tbl_quote
---------
quote_id pay_id quoted_amount
12 112 2000
13 113 3009
14 114 2005
15 115 1500
16 116 3000
17 117 4000

tbl_tbs
--------
tbs_id pay_id tbs_inspection_date tbs_description
12 112 10/10/2004 test-description
13 113 09/09/2004 test-description
14 114 12/10/2004 test-description
15 115 10/10/2004 test-description
16 116 12/12/2004 test-description
17 117 12/12/2004 test-description

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 13:52:18

and based on this sample data, the results you are looking to return are ..... ???

- Jeff

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 14:04:37

Module Count quote_count TBS_count Offer_count

Repay 65 14 3 50
payment 336 4 3 70

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 14:15:49

how can I return counts of 65 or 336 from your sample data? you've given about 10 rows. BASED ON YOUR SAMPLE DATA what results should be returned!? also I don't see anything about offers in your sample tables/data.

CREATE TABLE and INSERT statements would be nice; to test your solution and give you a nice answer I am going to have to type those all in myself...

- Jeff

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 14:19:50

sorry jeff I gave it from the original report....
Module Count quote_count TBS_count Offer_count

Repay 6 6 6 6
payment 6 6 6 6

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 14:23:54

and where does Offer Count come from ????

Are you sure this is good sample data to work with? returning counts of 6 all accross the board doesn't really seem like it will be a good indicator of a working SQL statement for you. part of the key is defining a good set of sample data that covers all possible situations.

and, of course, 99% of the time, if a programmer takes the time to isolate the problem, work on a small subset of data, and is able to word the requirements clearly in a sentence or two, you will find that they can easily solve the problem themselves.

- Jeff

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 14:53:24

Sorry for all the confusion....jeff....let me make it clear....hope this will help

tbl_repayment
-------------

pay_id initial_deposit due_date
112 200 12/12/2004
113 300 10/10/2004
114 100 09/09/2004
115 50 12/10/2004
116 233 11/12/1004
117 33 09/10/2004

tbl_payment
------------

pay_id payed_date payed_amount
112 10/11/2004 500
116 12/12/2004 600
117 10/10/2004 700

tbl_quote
---------
quote_id pay_id quoted_amount
15 115 1500
16 116 3000
17 117 4000

tbl_tbs
--------
tbs_id pay_id tbs_inspection_date tbs_description
12 112 10/10/2004 test-description
13 113 09/09/2004 test-description
14 114 12/10/2004 test-description
15 115 10/10/2004 test-description

Results

table_name Count quote_count TBS_count

tbl_repayment 6 3 4
tbl_payment 3 2 1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 15:05:23

see how easy it is when you help us help you?



-- here is the stuff
-- you should have provided (as is, more or less):

create table tbl_repayment (pay_id int, 
			initial_deposit money, 
			due_date datetime)

insert into tbl_repayment
select 112, 200, '12/12/2004' union
select 113, 300, '10/10/2004' union
select 114, 100, '09/09/2004' union
select 115, 50, '12/10/2004' union
select 116, 233, '11/12/2004' union
select 117, 33, '09/10/2004'



create table tbl_payment (pay_id int, 
			payed_date datetime, 
			payed_amount money)

insert into tbl_payment
select 112, '10/11/2004', 500 union
select 116, '12/12/2004', 600 union
select 117, '10/10/2004', 700

create table tbl_quote (quote_id int, 
			pay_id int, 
			quoted_amount money)

insert into tbl_quote
select 15, 115, 1500 union
select 16, 116, 3000 union
select 17, 117, 4000

create table tbl_tbs (tbs_id int, 
			pay_id int, 
			tbs_inspection_date datetime, 
			tbs_description varchar(100))

insert into tbl_tbs
select 12, 112, '10/10/2004','test-description' union
select 13, 113, '09/09/2004','test-description' union
select 14, 114, '12/10/2004','test-description' union
select 15, 115, '10/10/2004','test-description'


go

-- 
-- and here is your answer:
--

select 'tbl_repayment' as Table_Name, count(*) as Count, 
	(select count(*) from tbl_repayment a 
         inner join tbl_quote b on a.pay_id = b.pay_id) as Quote_Count,
	(select count(*) from tbl_repayment a 
         inner join tbl_tbs b on a.pay_id = b.pay_id) as TBS_Count
from tbl_repayment

union all

select 'tbl_payment' as Table_Name, count(*) as Count, 
	(select count(*) from tbl_payment a 
         inner join tbl_quote b on a.pay_id = b.pay_id) as Quote_Count,
	(select count(*) from tbl_payment a 
         inner join tbl_tbs b on a.pay_id = b.pay_id) as TBS_Count
from tbl_repayment

go

-- and now we clean the mess up:

drop table tbl_tbs
drop table tbl_quote
drop table tbl_payment
drop table tbl_repayment

And, of course, all of thos CREATE TABLE and INSERT and DROP TABLE statements would have been nice if you could have provided them, it took me an extra 10 minutes to give your answer because I had to type them all in. So, next time we see a question from you, you will give us all the info up front, right? Wouldn't you say it's worth it????

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 16:20:40

so did this work for you ???

- Jeff

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-10 : 16:48:06

It works......Thanks a lot jeff......Thanks...Next time I will provide u the create and insert..sorry for the making u write all these statements....thanks a lot.....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 17:27:19

It's Ok, I was giving you a hard time and I apologize ... I was just having one of those days, and got a little frustrated because I wanted to help you out. I'm glad it worked for you !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources