mysql - SQL statement for GROUP BY -

-

i stucked 1 sql select statement.

this output/result sql statement below:

enter image description here

what need: need have columns assignedvouchersnumber , usedvouchersnumber in 1 row msisdn. example if can see "msisdn" 723709656 there 2 rows now.. 1 assignedvouchersnumber = 1 , second assignedvouchersnumber = 1 too.

but need have in 1 row assignedvouchersnumber = 2. problem?

select eu.msisdn,    eu.id userid,     sum(case ev.voucherstate when '1' 1 else 0 end) assignedvouchersnumber,    sum(case ev.voucherstate when '2' 1 else 0 end) usedvouchersnumber,     ev.extra_offer_id,    ev.create_time,    ev.use_time,    ev.id voucherid,    ev.voucherstate   extra_users eu    join (select sn.msisdn telcislo,                 stn.numberid            stats_number sn                 join stats_target_number stn                   on ( sn.numberid = stn.numberid )           stn.targetid = 1) xy      on eu.msisdn = xy.telcislo    join extra_vouchers ev      on ( eu.id = ev.extra_user_id )  ev.create_time between '2012-07-23 00:00:00' , '2013-08-23 23:59:59'    , ev.use_time <= '2013-08-23 23:59:59'    , ev.use_time >= '2012-07-23 00:00:00'    , ev.voucherstate in ( 1, 2 )    , ifnull(ev.extra_offer_id in( 2335, 3195, 30538 ), 1)    group eu.msisdn, ev.extra_offer_id, ev.voucherstate order  eu.msisdn asc

you have 2 different extra_offer_id same msisdn , vouchersnumber. thats why 2 rows.


Comments

Post a Comment

Popular posts from this blog

Unable to remove the www from url on https using .htaccess -

-
i want remove www site url. ie. https://www.domainname.com.au https://domainname.com.au . i have tried various .htacces code nothing work me. here present code remove www http not work https. rewriteengine on rewritebase / rewritecond %{http_host} ^www\.(.*)$ [nc] rewriterule ^/(.*)$ http://%1/$1 [r=301,l] rewritecond %{https} off rewriterule ^(.*)$ https://domainname.com.au/$1 [r=301,l] rewriterule ^code-of-conducts/(.*)/$ code-of-conducts.php?type=$1 rewriterule ^parents/post-a-job/(.*)/edit/$ parents/post-a-job.php?action=edit&id=$1 rewriterule ^parents/post-a-job/(.*)/$ parents/post-a-job.php?job_type=$1 rewriterule ^parents/open-jobs/(.*)/delete/$ parents/open-jobs.php?action=delete&id=$1 rewriterule ^parent-profile/(.*)/$ parent-profile.php?id=$1 rewriterule ^parents/job-details/(.*)$ parents/job-details.php?applied_id=$1 rewriterule ^sitterprofile/(.*)/$ sitterprofile.php?id=$1 rewriterule ^sitter/job-board/state/(.*)/page/(.*)/$ sitter/job-board.ph...
Read more