Using different 'Where' conditions on multiple co

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

Using different 'Where' conditions on multiple co

Author

Topic

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 02:41:00

Hi

Does anyone know how to use multiple 'Where' conditions or 'Case' or something on multiple columns in the same table in one query? Like

select SUM(Col1+Col2) as SUM1 from TABLE1 where SUM(COl1+Col2)>5
select SUM(Col3+Col4) as SUM2 from TABLE1 where SUM(COl3+Col4)<10
etc

Hope you get my drift.

Regards
Sheraz

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-29 : 02:46:39

You want like this!

select sum1,sum2 from
(select SUM(Col1+Col2) as SUM1 ,SUM(Col3+Col4) as SUM2 from TABLE1) as tab
where sum1 >5 or/and sum2<10

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-29 : 02:49:03

Put aggregation conditions in HAVING clause instead of WHERE

HAVING SUM(COl1+Col2)>5

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 07:07:47

Small correction to myself.

I don't think I can use SUM(Col1+Col2) as SUM1.
Rather (Col1+Col2) as SUM1.

Also, why would I use 'as tab'?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 07:09:43

Still using Oracle?

Please post some proper sample data and expected output.

N 56°04'39.26"
E 12°55'05.63"

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 07:16:34

Sorry for posting to this forum again but as the thread is already under discussion, I thought I'd finish it first. Is that OK?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 07:19:37

Of course!
Please post some proper sample data and expected output.

N 56°04'39.26"
E 12°55'05.63"

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 07:22:13

OK. These are the queries that I want to combine into one query as all the counters are present in the same table:

1) Select DATETIME, BSC, CELL, (NVL(DROP,0) + NVL(DROPSUB,0) + NVL(TDROP,0) + NVL(HDROPSUB,0)) AS TCH_DROP from SCHEMA.TABLE
where (NVL(DROP,0) + NVL(DROPSUB,0) + NVL(TDROP,0) + NVL(HDROPSUB,0)) > 10

2) Select DATETIME, BSC, CELL, (ROUND((DIV(NVL(TAVAC,0),(NVL(TAVASC,0)*NVL(TNUCH,0))))) *100) AS AVAIL_PERCENT from SCHEMA.TABLE
where (ROUND((DIV(NVL(TAVAC,0),(NVL(TAVASC,0)*NVL(TNUCH,0))))) *100) < 90

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 07:23:45

You can use UNION ALL to combine two queries.

Select DATETIME, BSC, CELL, (NVL(DROP,0) + NVL(DROPSUB,0) + NVL(TDROP,0) + NVL(HDROPSUB,0)) AS TCH_DROP from SCHEMA.TABLE
where (NVL(DROP,0) + NVL(DROPSUB,0) + NVL(TDROP,0) + NVL(HDROPSUB,0)) > 10

union all

Select DATETIME, BSC, CELL, (ROUND((DIV(NVL(TAVAC,0),(NVL(TAVASC,0)*NVL(TNUCH,0))))) *100) AS AVAIL_PERCENT from SCHEMA.TABLE
where (ROUND((DIV(NVL(TAVAC,0),(NVL(TAVASC,0)*NVL(TNUCH,0))))) *100) < 90

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources