Question: I am getting the sum of all the rows in `registration` table. Table structure looks like this:

```
row_id  row_registration_fee  row_amount  row_class  row_user_id
1          200                 1000         18           1
2          200                 2510         18           1
3            0                 1600         19           2
4            0                 1500         19           1
5          200                 1254         19           3
6          200                 3000         19           1
7          200                 2000         19           1
8            0                  100         20           1
9            0                  300         20           2

```

A user can have multiple registration fee. And I need to get the sum of all the `row_registration_fee` by `row_class`. The result should be like this:

```
row_registration_fee  row_class
       200                18
       400                19
         0                20

```

My select :

```
SELECT (COUNT(DISTINCT(row_user_id))* 200) as 'fee' 
FROM registration 
WHERE row_registration_fee  > 0  
GROUP BY row_class

```

Is there a better query here that can give a result like the above sample? 

The result will be displayed in table rows using foreach loop in PHP. As of now, it will only give me two results for the registration fee, the `row_class 18` and `row_class 19` it excludes the `row_class 20` because it only selects the user with a fee.

**Additional explanation**: The user with 2 or more registration fees should count only as one if the user has a total of 400 fees it should sum only 200.

Answer: 
To get those expected results then it seems to me that you first need to get the unique records per (row\_registration\_fee, row\_class, row\_user\_id) tupple.  

You can use a sub-query with a DISTINCT for that.

Then sum the row\_registration\_fee.

```
SELECT 
 SUM(row_registration_fee) as fee, 
 row_class
FROM
(
   SELECT DISTINCT row_class, row_user_id, row_registration_fee
   FROM registration
) q
GROUP BY row_class

```

Or via a GROUP BY and get the MAX fee.

```
SELECT 
 SUM(max_fee) as fee, 
 row_class
FROM
(
   SELECT 
    row_class, row_user_id,
    MAX(row_registration_fee) as max_fee
   FROM registration
   GROUP BY 
    row_class, row_user_id
) q
GROUP BY row_class

```

But to fix your current query, you could remove that WHERE clause.  

And then use a CASE WHEN to return NULL on a zero row\_registration\_fee.  

Because a count by value doesn't count the NULL's.

```
SELECT 
 COUNT(DISTINCT CASE WHEN row_registration_fee = 0 THEN row_user_id END) * 200 as fee,
 row_class
FROM registration
GROUP BY row_class

```
```
   SELECT SUM(row_registration_fee), row_class, row_user_id
    FROM registration 
    WHERE row_registration_fee  > 0
    GROUP BY row_class, row_user_id;

```

Yeah, group by two columns you can get per class and user.