Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Joining two queries

Status
Not open for further replies.
nc297

nc297

Programmer
Apr 7, 2010
162
US
How do I join these two queries into one query?

SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendinfo1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt)
GROUP BY t.fo

union
SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
FROM t16pendmvt m join T16pendall t ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
group by t.fo
order by t.fo

 
Sort by date Sort by votes
Does that not work?
What error are you getting?

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Upvote 0 Downvote
Yes it works but I'm getting the results in this way.

FO PendinFO1
001 39
001 46
002 33
002 44
003 16
003 20
004 33
004 33

How do I get it to come out like this, no duplicates FO's but adding the results together?

FO PendinFO1
001 85
002 77
003 36
004 66
 
Upvote 0 Downvote
k. That's different.

Select a.FO, sum(a.PendingFO1) PendingFO1
FROM
( <<your query here >> ) as a
GROUP BY a.FO

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Upvote 0 Downvote
Trying this but it's not working. What am I doing wrong?

select t.fo, sum(t.pendinfo1) as PendinFO1
From
(SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendinfo1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt)
GROUP BY t.fo

union all
SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
FROM t16pendmvt m join T16pendall t ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
group by t.fo)a
order by t.fo
 
Upvote 0 Downvote
Posting the error message would be helpful.

You need to add an AS before the A

You need a group by OUTSIDE the derived table, and you have to refer to the columns as a.* now.



I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Upvote 0 Downvote
Code: 
select a.fo, sum(a.pendinfo1) as PendinFO1
From
(SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendinfo1
   FROM   t16pendall t  
     WHERE  (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
     and t.cos not in (select cos from t16pendmvt) 
     GROUP  BY t.fo

union all
  SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
     FROM t16pendmvt m join T16pendall t ON m.cos = t.cos
     WHERE  (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
group by t.fo)a
order by a.fo

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Sorry about that Qik3Coder I tried this but I'm getting the following errors below

select a.fo, sum(a.pendinfo1) as PendinFO1
From
(SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendinfo1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt)
GROUP BY a.fo

union all
SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
FROM t16pendmvt m join T16pendall t ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
group by a.fo) as a
order by a.fo


Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "a.fo" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "a.fo" could not be bound.
 
Upvote 0 Downvote
Gmmastros I tired yours but getting:

Column 'a.fo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It's referring to the first line.
 
Upvote 0 Downvote
Missed a group by. Try this:

Code: 
select a.fo, sum(a.pendinfo1) as PendinFO1
From   (
       SELECT t.fo, 
              ISNULL(COUNT(distinct t.cos), 0) AS pendinfo1
       FROM   t16pendall t  
       WHERE  (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
              and t.cos not in (select cos from t16pendmvt) 
       GROUP  BY t.fo

       union all

       SELECT t.fo, 
              Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
       FROM   t16pendmvt m 
              join T16pendall t 
                ON m.cos = t.cos
       WHERE  (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
       group by t.fo
       ) As a
[!]Group By a.fo[/!]
order by a.fo

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks George that worked. I wanted to add pendinFO1. I did this but got an error:

select a.fo, sum(a.pendinfo1) as PendinFO1, b.fo, sum(b.pendfoover1) as Pendfoover1
From (
SELECT t.fo,
ISNULL(COUNT(distinct t.cos), 0) AS pendinfo1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt)
GROUP BY t.fo

union all

SELECT t.fo,
Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
FROM t16pendmvt m
join T16pendall t
ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
group by t.fo
) As a
Group By a.fo

union all
SELECT t.fo,
ISNULL(COUNT(distinct t.cos), 0) AS PendinFO1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt) and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)
GROUP BY t.fo

union all

SELECT t.fo,
Isnull(COUNT(distinct t.cos), 0) AS PendinFO1
FROM t16pendmvt m
join T16pendall t
ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)
group by t.fo
) As b
Group By b.fo


Getting:

Incorrect syntax near ')'.

It is referring to ) As b

What am I missing? thanks so much
 
Upvote 0 Downvote
Count your open parenthesis ( and compare to the count of the close parenthesis ). Are they equal?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
nc297,

You're confusing your aliases.

the [!]a.[/!] only exists as part of the outermost select

select [!]a.[/!] from (
select t. from table t
union
select t. from table t)
as [!]a[/!]
GROUP BY [!]a[/!].
Order by [!]a[/!].

you still have to reference the inner items as the t. that you had prior.


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Upvote 0 Downvote
Thanks guys. I left work for the day and will check the procedure tonight and will try it in the morning.
 
Upvote 0 Downvote
Ugh...I looked all over and it still comes up with the same error.

Could it be pertaining to the first open ( here:

select a.fo, sum(a.pendinfo1) as PendinFO1, b.fo, sum(b.pendfoover1) as Pendfoover1
From (


Alone it runs fine just can't join them.
 
Upvote 0 Downvote
Post whole query again.
Post the actual red text error that you get.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Upvote 0 Downvote
Error message:

Msg 102, Level 15, State 1, Line 40
Incorrect syntax near ')'.

select a.fo, sum(a.pendinfo1) as PendinFO1, b.fo, sum(b.pendfoover1) as Pendfoover1
From (
SELECT t.fo,
ISNULL(COUNT(distinct t.cos), 0) AS pendinfo1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt)
GROUP BY t.fo

union all

SELECT t.fo,
Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
FROM t16pendmvt m
join T16pendall t
ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
group by t.fo
) As a
Group By a.fo

union all
SELECT t.fo,
ISNULL(COUNT(distinct t.cos), 0) AS PendinFO1
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt) and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)
GROUP BY t.fo

union all

SELECT t.fo,
Isnull(COUNT(distinct t.cos), 0) AS PendinFO1
FROM t16pendmvt m
join T16pendall t
ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)
group by t.fo
) As b
Group By b.fo


Thanks!

It works alone but not when I added the other one.
 
Upvote 0 Downvote
You can't do what you want to do, like that.
If you
Code: 
select a.fo, sum(a.pendinfo1) as PendinFO1 [!]--Stuff removed from here[/!]
From   (       
	SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0) AS pendinfo1       
	FROM t16pendall t 
	WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')) 
		and t.cos not in (select cos from t16pendmvt)        
	GROUP  BY t.fo
	
	union all
	
	SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS pendinfo1
	FROM t16pendmvt m 
	join T16pendall t ON 
		m.cos = t.cos       
	WHERE  (mvt_typ = 'R' or mvt_typ='T' ) 
		and not (mvt_loc LIKE '[RSV]%')       
	group by t.fo       ) As a
Group By a.fo

union all 

[!]SELECT b.fo, sum(b.pendfoover1) as Pendfoover1
From   (       [/!]
	SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0) AS PendinFO1       
	FROM   t16pendall t         
	WHERE  (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))              
		and t.cos not in (select cos from t16pendmvt)  
		and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)       
	GROUP  BY t.fo       
	union all       
	SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS PendinFO1       
	FROM   t16pendmvt m
	join T16pendall t ON 
		m.cos = t.cos       
	WHERE  (mvt_typ = 'R' or mvt_typ='T' ) 
		and not (mvt_loc LIKE '[RSV]%') 
		and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)       
	group by t.fo) As b
Group By b.fo

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Upvote 0 Downvote
Thanks so much for your help!!! When I tried it only the one column came up Pendinfo1.

So someone helped me get this to work:

Select b.fo, sum(b.pendinfo1) as pendinfo1, sum(b.pendover) as pendover, sum(b.pend250) as pend250
from
(
SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendinfo1,
ISNULL(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) > 300 then t.cos else null end), 0)AS pendover,
ISNULL(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) Between 249 and 301 then t.cos else null end), 0)AS pend250
FROM t16pendall t
WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.cos not in (select cos from t16pendmvt)
GROUP BY t.fo

union all
SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS pendinfo1,
Isnull(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) > 300 then t.cos else null end), 0) AS pendover,
ISNULL(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) Between 249 and 301 then t.cos else null end), 0)AS pend250
FROM t16pendmvt m join T16pendall t ON m.cos = t.cos
WHERE (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
group by t.fo
) As b
Group By b.fo
order by b.fo
 
Upvote 0 Downvote
Are you good now?


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ms901Boss
  • Locked
  • Question
Joining two select statement into one
Replies
1
Views
86
kray4660
kray4660
briangriffin
  • Locked
  • Question
Calculating differences every two rows
Replies
2
Views
180
kjv1611
kjv1611
Cpreston
  • Locked
  • Question
Joining 2 cast fields together 1
Replies
5
Views
119
Cpreston
Cpreston
Niki_S
  • Locked
  • Question
Create view using two databases
Replies
4
Views
149
Chris Miller
Chris Miller
ms901Boss
  • Locked
  • Question
Join two select statements and collecting data from both
Replies
13
Views
150
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top