CODEWITHSUNDEEP
sqloracle-database
Lactose
Lactose

Reputation: 695

is there a way to apply WHERE conditions to only one field in a select?

Short of doing something like this

edited 6/29/2012 at 10:17 AM I realized that my query is a little more complicated than my example query and the current suggestions aren't going to work, but that's my fault. I have created a more accurate query above the original.

SELECT DISTINCT
a,
max(b) as b,
(select count(distinct c) as c from d where e=2) as c
FROM d
GROUP BY a

sample data from d

 a | b | c | e
---+---+---+---
 0 | 0 | 0 | 1
 0 | 0 | 0 | 2
 1 | 0 | 0 | 1
 1 | 0 | 0 | 1

so for this set of data, where a = 0 c would be a count of 1 (where e==1) and for a = 1 c would be a count of 2 (where e==1)

end of edit

SELECT
a,
b,
(SELECT c from d where e=2)
FROM d

Or at the least is there a better way of doing this performance wise? Like a union perhaps.

Upvotes: 1

Views: 112

Answers (3)

Dave Costa
Dave Costa

Reputation: 48121

As I understand it you want to group by a. For each group you want the maximum value of b, and you want to know the number of distinct values of c in rows where e=2.

Other answers have the right idea in using CASE but have not addressed that you are looking to count distinct values. I think this is what you are looking for:

SELECT a, MAX(b), COUNT(DISTINCT CASE WHEN e=2 THEN c else NULL end)
  FROM d
  GROUP BY a

(Your question at the moment is unclear as to whether the value of e you are after is 1 or 2; I went with 2 in the query above.)

Upvotes: 0

int2000
int2000

Reputation: 565

Based on new Information, try this:

SELECT a,
       max(b) as b,
       sum(case when e=2 then 1 else 0 end) as c
FROM d 
GROUP BY a

Upvotes: 0

Phil
Phil

Reputation: 497

if I understand what you're asking then this would work:

SELECT
  a,
  b,
  case
    when e=2 then c
    else null
  end as c
FROM
  d

Upvotes: 2

Related Questions