Question: I am new to Oracle DB and I am trying to write a query which will count SUM of rows, which have field AWARD between 3 and 4, however then I need to divide them by COUNT() of all rows. This means i need to count even rows where AWARD = 1,0,-1 etc.

Here is my query, which I tried to use:

```
SELECT SUM(AWARD)/COUNT(*) FROM EVALUATION
WHERE AWARD BETWEEN 2 AND 4 ;

```

However, I understand that I am getting COUNT only for rows which are suitable for WHERE clause. What should I do? Also, I need to add SUM of AWARD's with value 2, however, just 50% of it.

Answer: 
You can use conditional aggregation with `AVG()`:

```
SELECT AVG(CASE WHEN AWARD BETWEEN 2 AND 4 THEN AWARD ELSE 0 END)
FROM EVALUATION;

```

Of course, you can do the division yourself, if you really, really want to:

```
SELECT SUM(CASE WHEN AWARD BETWEEN 2 AND 4 THEN AWARD ELSE 0 END) / COUNT(*)
FROM EVALUATION;

```

EDIT (in response to comment):

```
SELECT AVG(CASE WHEN AWARD = 2 THEN AWARD / 2
                WHEN AWARD BETWEEN 2 AND 4 THEN AWARD -- although 2 matches, it is caught in the first condition
                ELSE 0
           END)
FROM EVALUATION;

```
Try this

```
SELECT SUM(CASE WHEN AWARD BETWEEN 2 AND 4 THEN AWARD ELSE 0 END)/COUNT(*) 
FROM EVALUATION

```