sql - MySQL count rows from multiple tables using join -

-

i have 3 mysql tables:

**locations** id      location_name 1       london 2       new york 3       washington  **bookings** id      voucher_code    location_id 1       disc100         1 2       null            null 3       disc200         1 4       disc500         2 5       disc700         1  **vouchers** id      voucher_code    net_value 1       disc100         155.00 2       disc200         135.00 2       disc500         155.00 2       disc700         155.00

i'm trying display count of voucher net values each location. example can see 'london' location has 3 vouchers have been redeemed in bookings table, of 2 155.00 values vouchers , 1 135.00. need write mysql query pull these counts out of database.

so using above tables example, sql query should result in following:

location            netvalue155     netvaue135 london              2               1 new york            1               0 washington          0               0

below have written far:

select      l.location_name,     '' date_from,      '' date_to,     v.netvalue155 locations l  left join (select voucher_code, count(distinct id) netvalue155 vouchers net_value='155.00' group id) v 

select a.location_name, sum(netvalue155), sum(netvalue135) (select l.id ,l.location_name, case when v.net_value = 155 1 else 0 end netvalue155, case when v.net_value = 135 1 else 0 end netvalue135 locations l left join bookings b on l.id = b.location_id left join vouchers v on  b.voucher_code = v.voucher_code) right join locations l on l.id = a.id group a.location_name

[sql fiddle] http://sqlfiddle.com/#!9/daefd/19


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more