Combine 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

SQL Server 2000 Forums

SQL Server Development (2000)

Combine Query

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-15 : 12:11:37

How can I combine these 2 queries into one?

SELECT COUNT(User_Def_Fld_5)
FROM OEORDHDR_SQL
WHERE Slspsn_No = 102
AND User_Def_Fld_5 = 'Outbound'
AND OEORDHDR_SQL.Ord_dt >20040701

SELECT COUNT(User_Def_Fld_5)
FROM OEHDRHST_SQL
WHERE Slspsn_No = 102
AND User_Def_Fld_5 = 'Outbound'
AND OEHDRHST_SQL.Ord_dt >20040701

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 12:29:56

Do you want TWO rows, one for each SELECT?

If so put UNION ALL between then (and add a first column saying 'OEORDHDR_SQL' / 'OEHDRHST_SQL' or somesuch so that you know which table they came from.

If you mean you want to ADD the two COUNTs together then do


SELECT
COALESCE((SELECT COUNT(User_Def_Fld_5)
FROM OEORDHDR_SQL
WHERE Slspsn_No = 102
AND User_Def_Fld_5 = 'Outbound'
AND OEORDHDR_SQL.Ord_dt >20040701), 0)
+
COALESCE((SELECT COUNT(User_Def_Fld_5)
FROM OEHDRHST_SQL
WHERE Slspsn_No = 102
AND User_Def_Fld_5 = 'Outbound'
AND OEHDRHST_SQL.Ord_dt >20040701), 0)

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-15 : 12:32:53

my syntax might be a little off, it is early and I haven't had any coffee yet. Give this a shot:

SELECT a.Kount, b.Kount
FROM
  (SELECT COUNT(User_Def_Fld_5) as Kount
   FROM OEORDHDR_SQL
   WHERE Slspsn_No = 102
   AND User_Def_Fld_5 = 'Outbound'
   AND OEORDHDR_SQL.Ord_dt >20040701) a,
  (SELECT COUNT(User_Def_Fld_5) as Kount
   FROM OEHDRHST_SQL
   WHERE Slspsn_No = 102
   AND User_Def_Fld_5 = 'Outbound'
   AND OEHDRHST_SQL.Ord_dt >20040701) b

-ec

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-15 : 12:58:14

Both of those worked, but I liked Kristen's way. Because it added them together. What does COALESCE mean? Add together?

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-15 : 13:01:21

COALESCE will provice you a predefined value in case the value is null

DECLARE @x char(1)
SELECT COALESCE(@x,'x'), ISNULL(@x,'x')

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 13:11:31