Making this one query - 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

General SQL Server Forums

New to SQL Server Programming

Making this one query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-28 : 13:16:43

How can I make these two queries one query? I tried union but that didn't work as I got two results


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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-28 : 13:49:28

Tried this but getting incorrect syntax near the keyword group:


SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendinfo1
   FROM  t16pendall t
    WHERE  (t.mft_POSN1_CD in('b','d') or (t.MFT_POSN1_CD='a' and t.aged_alien_rsw='y'))
     and t.cos not in (select cos from t16pendmvt) 
(Select m.cos
from t16pendmvt m
where m.cos=t.cos and 
 (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%'))
group by t.fo

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-28 : 20:58:35

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

without any DDL, all I can do is guess that the UNION can be replaced by OR

SELECT T.fo, COALESCE(COUNT(DISTINCT T.cos), 0) AS pendinfo1
FROM T_16_All_mvt AS M,
T_16_All_all AS T
WHERE (mft_posn1_cd IN ('b', 'd', 'a' )
AND aged_alien_rsw = 'y'))
AND T.cos NOT IN (SELECT cos FROM T_16_All_mvt))
OR (M.cos = T.cos
AND mvt_typ IN ('r', 't')
AND mvt_loc NOT LIKE '[rsv]%')
GROUP BY T.fo;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 01:15:03

if you want to merge both query resultset to one then you should use JOIN. The type of join depends on how your requirement. AS Celko pointed out some sample data and table structure will help to identify what you're after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-29 : 09:22:23

Sorry about that...

The results are coming out as this:



FO      Pendinfo1
001	39
001	46
002	33
002	44
003	16
003	20
004	33
005	4
005	16
006	8
006	16

I want the above query to produce this:


FO     Pendinfo1
001	85
002	77
003	36
004	33
005	20
006	24

It's hard to produce what I want in a table so you can see what I'm doing. I hope this is enough information.

I am trying to have the results come out in the second table rather than the first table. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 11:35:43

[code]
select fo,
SUM(Pendinfo1) as Pendinfo1
from
(
your first query
union all
your second query
)p
group by fo
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-29 : 12:24:57

Thanks that worked!!!

I tried to add another column but I'm getting incorrect syntax near ')'


Select p.fo, sum(p.pendinfo1) as pendinfo1, sum(b.pendover) as pendover
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 p
Group By p.fo


union all

SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendover
   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 pendover
     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

It's referring to bold section

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 12:44:37

[code]
Select b.fo, sum(b.pendinfo1) as pendinfo1, sum(b.pendover) as pendover
from
(
SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendinfo1, ISNULL(COUNT(distinct t.cos), 0)AS pendover
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 t.cos), 0) AS pendover
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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 12:47:10

i didnt understand why you've same calculation for both the pendinfo1 and pendover columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-29 : 13:17:45

You're right I didn't add this to the last two queries:

and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)




Select p.fo, sum(p.pendinfo1) as pendinfo1, sum(b.pendover) as pendover
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 p
Group By p.fo


union all

SELECT t.fo, ISNULL(COUNT(distinct t.cos), 0)AS pendover
   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 pendover
     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
order by b.fo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 13:22:43

it should be like this


Select b.fo, sum(b.pendinfo1) as pendinfo1, sum(b.pendover) as pendover
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
   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
     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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-29 : 14:03:04

THANK YOU SO MUCH!!!! That's exactly what I needed.

I have two more queries to add.

I didn't send them all as I didn't want to confuse you.

Is it best to just put these queries into it's own query?

I need to get pendindds

SELECT t.fo, Isnull(COUNT(distinct t.cossn), 0) AS pendndds
    FROM t16pendmvt m JOIN t16pendall t ON m.cos = t.cos 
    WHERE (mvt_typ = 'R') and (mvt_loc LIKE '[RSV]%')
    GROUP BY t.fo

Then Penddds > 249 and < 301

Select t.fo, Isnull(count(distinct t.cossn),0) as pendinDDS250
From t16pendmvt m join t16pendall t on m.cos = t.cos
Where (mvt_typ='r') and (mvt_loc LIKE '[RSV]%')
And (Datediff(Day, conv_flg_cdt, Getdate()) > 249)
And (Datediff(Day, conv_flg_cdt, Getdate()) < 301)

How could I add them to this query or should I dump them in a separate table and join the tables to get the totals


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.cossn), 0)AS pendinfo1, 
ISNULL(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) > 300 then  t.cossn else null end), 0)AS pendover,
ISNULL(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) Between 249 and 301 then  t.cossn 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.cossn not in (select cossn from t16pendmvt) 
     GROUP  BY t.fo

union all

  SELECT t.fo, Isnull(COUNT(distinct t.cossn), 0) AS pendinfo1, 
Isnull(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) > 300 then  t.cossn else null end), 0) AS pendover,
ISNULL(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) Between  249 and  301 then  t.cossn else null end), 0)AS pend250     
FROM t16pendmvt m join T16pendall t ON m.cossn = t.cossn
 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 14:08:28

now this is an exercise for you

.
just follow the pattern i showed and try to add it yourself. If you face any issues, post and we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-29 : 14:33:49

You are correct I will try and post if I need help. Off to try it. Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-07-29 : 15:13:24

Okay I'm stuck and don't know what I'm doing wrong:

Here's my error:


Msg 8120, Level 16, State 1, Line 1
Column 'T16pendall.FO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pendindds'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pendddsover'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'penddds250'.

Here's the query


Select b.fo, sum(b.pendinfo1) as pendinfo1, sum(b.pendover) as pendover, sum(b.pend250) as pend250, sum(b.pendindds) as pendindds, sum(b.pendddsover) as pendddsover,
sum(b.penddds250) as penddds250
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.cossn 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

union all

SELECT t.fo, Isnull(COUNT(distinct t.cos), 0) AS pendindds, 
Isnull(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) > 300 then  t.cos else null end), 0) AS penddsover,
ISNULL(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) Between  249 and  301 then  t.cos else null end), 0)AS pendds250     
FROM t16pendmvt m join T16pendall t ON m.cos = t.cos
 Where (mvt_typ='r') and (mvt_loc LIKE '[RSV]%')

       ) As b
Group By b.fo
order by b.fo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 01:22:54

ok here you go. compare and see where you went wrong


Select b.fo, sum(b.pendinfo1) as pendinfo1, sum(b.pendover) as pendover,sum(pendndds) as pendndds,sum(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,CAST(NULL as int) AS pendndds,CAST(NULL as int) 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,NULL,NULL
     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
  
union all

 SELECT t.fo,NULL,NULL, Isnull(COUNT(distinct t.cossn), 0) AS pendndds,
Isnull(COUNT(distinct case when Datediff(Day, conv_flg_cdt, Getdate()) > 249 and Datediff(Day, conv_flg_cdt, Getdate()) < 301 then  t.cossn else null end), 0) as pend250
    FROM t16pendmvt m JOIN t16pendall t ON m.cos = t.cos 
    WHERE (mvt_typ = 'R') and (mvt_loc LIKE '[RSV]%')
    GROUP BY t.fo    
       ) As b
Group By b.fo
order by b.fo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-02 : 11:03:01

Thanks I'm on vacation until Wednesday I will check it out tomorrow to see where I went wrong. I also have a couple of questions to ask why certain things were done a certain way. Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 11:47:54

ok no probs.
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-03 : 15:06:23

Thanks so much I'm understanding it somewhat...

So you put the Null's in because you needed a spacer and each select has to have the same amount of items in it?

I see that I can use the same column names (pendover, pend250 etc...)since they are in different where clauses and I'm summing all up at the top.

Could you explain this statement to me please, mainly the bold part.

Isnull(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) > 300 then t.cos else null end), 0) AS pendover

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 08:39:35

quote:
Originally posted by JJ297

Thanks so much I'm understanding it somewhat...

So you put the Null's in because you needed a spacer and each select has to have the same amount of items in it?
Exactly. Union all requires same number ofcolumns and corresponding datatypes should also be same
I see that I can use the same column names (pendover, pend250 etc...)since they are in different where clauses and I'm summing all up at the top.
correct again
Could you explain this statement to me please, mainly the bold part.

Isnull(COUNT(distinct case when Datediff(DAY, t.flg_cdt, Getdate()) > 300 then t.cos else null end), 0) AS pendover
its checking if additional condition is satisfied and adding those records to count and ignoring others (NULLs wont add up to count values)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-04 : 10:18:07

Great thanks so much for the lesson!!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources