CODEWITHSUNDEEP
mysqlsql
Eric
Eric

Reputation: 3040

Count on a calculated row in mysql

This is probably a naughty use for sql... But here it goes....

I have a table:

Banners
--------------
BannerID
request_t0
clicks_t0
request_t1
clicks_t1
...
request_t6
clicks_t6

Using the number of requests and clicks, I calculate a ctr (clicks to impressions ratio) for each set.... ctr_t0 = clicks_t0 / request_t0 * 100

So now I have 6 separate CTRs in each row....

For output, I would like the count of how often each CTR is the highest in it's row...

So given the set:

ctr_t0    ctr_t1    ctr_t3
------    ------    ------
 2.39%     1.24%      1.5%
  1.4%     2.46%      2.2%
  3.1%     2.45%     1.45%

I would like as my result:

ctr_t0_count    ctr_t1_count    ctr_t3_count
------------    ------------    ------------
           2               1               0

Any ideas on how to do this w/o learning a programing language? :-)

Upvotes: 1

Views: 187

Answers (1)

Nicola Cossu
Nicola Cossu

Reputation: 56357

select
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t0 then 1 else 0 end) as ctr_t0_count,
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t1 then 1 else 0 end) as ctr_t1_count,
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t3 then 1 else 0 end) as ctr_t3_count
from (select .... ) as t

where within select there is your previous query.

Upvotes: 2

Related Questions