SQL divide data in rows

Question

I have the following table named

Table: activity

userid    appid    type
1         a        imp
2         a        imp
2         a        click
3         a        imp
4         a        imp
4         a        click
5         b        imp
5         b        click

I am trying to calculate the click-through rate for each appid. In this instance we define a click-through as the (number of clicks) / (number of impressions). I have written the following SQL:

SELECT appid, type, count(*) from activity group by appid, type

and got the following outcome:

Output:

appid    type       count(*)
a        click      2
a        imp        4
b        click      1
b        imp        1

The next step is to do a row-wise division. Ultimately, I would like to achieve the following:

Goal:

appid    click-through
a        .5                        # 2/4 = .5
b        1                         # 1/1 = 1

How is this achieved? Ideally I want this to be done in one query, is that possible?

Answer 1

You can use conditional aggregation to do this:

   select appid
        , SUM(CASE WHEN type = 'click' THEN 1 END)*1.0
        / SUM(CASE WHEN type = 'imp' THEN 1 END) AS click_through
    from activity
    group by appid

Demo: SQL Fiddle

If using MySQL you can further simplify with:

   select appid
        , SUM(type = 'click')*1.0
        / SUM(type = 'imp') AS click_through
    from activity
    group by appid