issue with union

[SqlHunter]

table Count quote_count TBS_count Offer_count

Repay 65 14 3 50
payment 336 4 10 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

 

[SqlHunter]

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

something like this but I can't get the quote_count,TBS_count and offer_count as needed..how will get the count inside the select query

 

[SQLSister]

You are missing commas after TBS_count in both selects

Questions about posting. See faq183-874

 

[SqlHunter]

I changed it..still the issue remains to get the count from different tables.hope this will help 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

 

[donutman]

Do you want the output to look like your 1st post? Just the 2 rows and the various counts for each row?
That can be done, but would like to take a stab at the SQL necessary for each count seperately...then we can show you how to combine and summarize it so that you only get the 2 rows. All in 1 query!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

oops. I left out the most important word. "...but would you like..."
-Karl

 

[SqlHunter]

Yes that will be fine..Thanks donutman

 

[donutman]

Kewl, show me the SQL that you got for each count and I'll explain how to merge it into 1 query.
-Karl

 

[donutman]

[Blue]SELECT[/Blue] RowType[Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]CountA[Gray])[/Gray][Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]CountB[Gray])[/Gray][Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]CountC[Gray])[/Gray][Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]CountD[Gray])[/Gray] [Blue]FROM[/Blue]
   [Gray]([/Gray][Blue]SELECT[/Blue] [red]'Payment'[/red] [Blue]AS[/Blue] RowType[Gray],[/Gray] 
          [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray] [Blue]AS[/Blue] CountA[Gray],[/Gray] 
          0 [Blue]AS[/Blue] CountB[Gray],[/Gray] 
          0 [Blue]AS[/Blue] CountC[Gray],[/Gray] 
          0 [Blue]AS[/Blue] CountD
       [Blue]FROM[/Blue] www [Blue]UNION[/Blue] [Gray]ALL[/Gray]
    [Blue]SELECT[/Blue] [red]'Payment'[/red][Gray],[/Gray] 0[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray][Gray],[/Gray] 0[Gray],[/Gray] 0 [Blue]FROM[/Blue] xxx [Blue]UNION[/Blue] [Gray]ALL[/Gray]
    [Blue]SELECT[/Blue] [red]'Repay'[/red][Gray],[/Gray]   0[Gray],[/Gray] 0[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray][Gray],[/Gray] 0 [Blue]FROM[/Blue] yyy [Blue]UNION[/Blue] [Gray]ALL[/Gray]
    [Blue]SELECT[/Blue] [red]'Repay'[/red][Gray],[/Gray]   0[Gray],[/Gray] 0[Gray],[/Gray] 0[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray] [Blue]FROM[/Blue] zzz [Blue]UNION[/Blue] [Gray]ALL[/Gray][Gray])[/Gray] DT
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] RowType

The first thing to notice is that the 1st SELECT is being performed on a Derived Table that is set-off with "(Select ...) DT" and since it's an aggregate query (uses SUM's) it will cut down the number of rows based upon the GROUP BY clause.
Now, take a look at the derived table that I called DT. Notice how I've named the columns in the 1st SELECT clause and put zeros in the counts that need to be calculated later. I also put in a literal string to represent the RowType. In the subsequent SELECT clauses I've moved the COUNT() to the appropriate location.
Hope this helps.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

very nice, karl

i love the colour coding -- do you do that by hand or do you have some type of utility?

by the way you have an extra UNION ALL at the end there

rudy

http://r937.com/

SQL Consulting

 

[donutman]

UNION oops! I wrote a program that runs in the background. When I hit a hot key, it converts the contents of the clipboard adding the TGML tags and changes the case of the keywords appropriately. I'm anal, but not anal enough to do it manually.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]