mysql - I can not do this query without nested select -


i have table 3 attributes (identificator, id_law, surname, vote). vote can take 3 values: positive, against or abstained.

i can not query: want know every law there votes in positive, against , abstentions.

the select must unique , not allowed nested select

create table house_law (   -- proposed vote law_id varchar(50) not null primary key, descr varchar(255) );  insert house_law (law_id,descr) values ('hr-109b','forbid driving near turtles');  create table house_rep (   -- session monday friday, captures start date rep_id int not null primary key, fullname varchar(80) not null, party varchar(80) );  insert house_rep (rep_id,fullname,party) values (1001,'thomas jefferson','whig'); insert house_rep (rep_id,fullname,party) values (800,'fred','abstain party'); insert house_rep (rep_id,fullname,party) values (700,'stan','abstain party');  create table votes_cast (law_id varchar(50) not null, rep_id int not null, the_vote varchar(20) -- for, against, abstain );  insert votes_cast (law_id,rep_id,the_vote) values ('hr-109b',1001,'against'); insert votes_cast (law_id,rep_id,the_vote) values ('hr-109b',800,'abstain'); insert votes_cast (law_id,rep_id,the_vote) values ('hr-109b',700,'abstain');  -- results  select l.law_id, l.descr, sum(case when v.the_vote='for' 1 else 0 end) for_votes, sum(case when v.the_vote='against' 1 else 0 end) against_votes, sum(case when v.the_vote='abstain' 1 else 0 end) abstain_votes house_law l join votes_cast v on v.law_id=l.law_id group l.law_id -- or close 1 law in particular

output: id | descr | votes_for | against | abstain

hr-109b forbid driving near turtles 0 1 2


Comments

Post a Comment

Popular posts from this blog

node.js - Using Node without global install -

i'm working on few node & electron (atom shell) projects , i'm curious how them work without user having install node globally. i've seen few applications including node directory in project contains node.exe binary. how go setting node project use binary? edit: thought should add, i've searched everywhere answer this, unrelated results.
Read more

How to access a php class file from PHPFox framework into javascript code written in simple HTML file? -

i've website developed using phpfox v3.0.7 . now i'm trying implement 'sse(server sent events)' 1 of php files. reference i'm giving below necessary code part of file. file titled process.class.php , present @ location /var/www/module/notification/include/service/process.class.php present on server having ip http://34.124.40.142/ . <?php /** * [phpfox_header] */ header('content-type: text/event-stream'); header('cache-control: no-cache'); defined('phpfox') or exit('no dice!'); /** * * * @copyright [phpfox_copyright] * @author raymond benc * @package phpfox_service * @version $id: service.class.php 67 2009-01-20 11:32:45z raymond_benc $ */ class notification_service_process extends phpfox_service { /** * class constructor */ public function __construct() { $this->_stable = phpfox::gett('notification'); } public function add($...
Read more

java - Null response to php query in android, even though php works properly -

Image
table i'm trying retrieve above data using 2 php files below. want pass email parameter , return bets user = email. work fine when tested advanced rest client, problem not php. however, when try through android 2 java classes below, null pointer exception (logcat below) , json object returned null. i'm not sure error is, think i'm not passing parameter php , that's why it's producing null response. logcat 05-31 15:10:50.227 26074-26074/com.example.albert.betterapp e/androidruntime﹕ fatal exception: main process: com.example.albert.betterapp, pid: 26074 java.lang.runtimeexception: unable start activity componentinfo{com.example.albert.betterapp/com.example.albert.betterapp.displayallbets}: java.lang.nullpointerexception @ android.app.activitythread.performlaunchactivity(activitythread.java:2292) @ android.app.activitythread.handlelaunchactivity(activitythread.java:2350) @ android.app.activitythread.acce...
Read more