Thread: Using a calculated column in the same query

1. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2011
Posts
53
Rep Power
4

Using a calculated column in the same query

Hi all

I'm sure this has been asked before but I can't find any obvious answers.

This query doesn't work; I'm going to try a subquery but is there a better way to achieve this?

Code:
```  SELECT DISTINCT ID
, sum (charge1)
+ sum (charge2)
+ sum (charge3)
- sum (refund1) AS revenuetotal
FROM contracts
WHERE date BETWEEN '2012-04-01' AND '2012-04-30'
AND revenuetotal != 0
AND customerid='123'
GROUP BY ID```
thank you
2. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2011
Posts
53
Rep Power
4
I think I've solved this:

Code:
```  SELECT DISTINCT ID
, sum (charge1)
+ sum (charge2)
+ sum (charge3)
- sum (refund1) AS revenuetotal
FROM contracts
WHERE date BETWEEN '2012-04-01' AND '2012-04-30'
AND revenuetotal != 0
AND customerid='123'
GROUP BY ID
HAVING (sum (charge1)
+sum (charge2)
+sum (charge3)
-sum (refund1)) <> 0```
At least, it works - I'm not sure if it's the most efficient solution, any comments welcome.

Thanks
3. you could also use the inline view approach
Code:
```SELECT id
, revenuetotal
FROM ( SELECT id
, sum (charge1) +
sum (charge2) +
sum (charge3) -
sum (refund1) AS revenuetotal
FROM contracts
WHERE date BETWEEN '2012-04-01' AND '2012-04-30'
AND customerid = '123'
GROUP
BY id ) AS v
WHERE revenuetotal <> 0```
this is a useful technique whenever you need to reference a complex expression using a column alias in the WHERE clause when there is no GROUP BY clause that allows you to use HAVING

but the main reason i'm replying is to tell you that your use of DISTINCT was redundant

using GROUP BY produces distinct rows by definition
4. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Mar 2011
Posts
53
Rep Power
4
Thanks Rudy

That's a really useful technique, as you say. Am I right in thinking that the use of 'v' as the name of the view is just an arbitrary character, in this case being the initial of view? Could it just as easily be 'bananas' or whatever, provided it's a unique string?

Also, thank you for explaining how DISTINCT is not required. I didn't realise that GROUP BY has the same effect.