How to do SQL calculation from a view?

Question

I have a view that generate the query result like below:

channel | current_Month | total_month_count | type | total_type_count |
-----------------------------------------------------------------------
chaA    |             2 |                 1 |    N |                1 |
-----------------------------------------------------------------------
chaB    |             2 |                 2 |    N |                2 |
-----------------------------------------------------------------------
chaA    |             2 |                 3 |    Y |                3 |
-----------------------------------------------------------------------

I would like to make a query on the view that could retrieve the value of total_month_count of type 'Y' to minus the value of total_month_count of type 'N' under the same channel group. In this case, there are 2 rows will get affect which is row 1 and row 3, which will be 3 minus 1, and the result will be equivalent to 2.

How could I make a query that could return me the result of 2 base on this view in Informix? Lets not limit to Informix, any other DB will do, I'm just curious to know whether are there any solution to do it in SQL?

GarethD · Accepted Answer · 2012-03-06T10:30:41.167

3

SELECT  Channel,
        SUM(CASE WHEN Type = 'N' THEN -1 ELSE 1 END * total_month_count) "Calculation"
FROM    yourView
GROUP BY Channel

EDIT 2

Having given this more thought (After a gentle nudge from Mark bannister) and some testing I have removed the Join versions from my answer. It is less efficient than grouping.

edited Mar 06 '12 at 10:30

answered Mar 06 '12 at 09:47

GarethD

68,045
10
83
123

2

I would expect the SUM form to be faster, since it only requires a single logical read of the table. Also, Informix uses double-quotes (`"`) rather than square brackets (`[]`) to quote identifiers. – Mar 06 '12 at 10:01
@MarkBannister You are certainly correct. I have changed the square brackets to double quotes and removed the join solution from the answer as some quick testing showed it to be much less efficient. I was confusing logic with that encountered [Here](http://stackoverflow.com/questions/9376514/sql-select-query-for-complex-dynamic-rows/9376969#9376969) – GarethD Mar 06 '12 at 10:33
Does this count 'chaB' even though it doesn't have a 'Y' entry? – Jonathan Leffler Mar 06 '12 at 20:33
1

Yes. If you need to only include channels that have a Y entry add `HAVING COUNT(CASE WHEN Type = 'Y' 1 END) > 0` to the end of the statement, or if it needs to include only channels with both a Y and N entry add `HAVING(COUNT DISTINCT Type) > 1` – GarethD Mar 06 '12 at 20:42

score 2 · Answer 2 · edited Mar 06 '12 at 20:33

2

Assuming your view name is tab_values

select ty.total_month_count - tn.total_month_count, ty.channel 
from tab_values ty, tab_values tn
where ty.channel = tn.channel
and ty.type = 'Y' 
and tn.type = 'N'

edited Mar 06 '12 at 20:33

Jonathan Leffler

730,956
141
904
1,278

answered Mar 06 '12 at 09:32

Chetter Hummin

6,687
8
32
44

score 1 · Answer 3 · answered Mar 06 '12 at 10:00

I would try something like the following :

SELECT channel,
       SUM(CASE WHEN TYPE = 'Y' THEN total_month_count ELSE 0 END -
       CASE WHEN TYPE = 'N' THEN total_month_count ELSE 0 END )
  FROM t
 GROUP BY channel

It would returns you total_month_count where type = 'Y' minus total_month where type = 'N' grouped by channel, which means if for a channel you have only type 'N' it would return a negative value.

How to do SQL calculation from a view?

3 Answers3