python 2.7 - SQL Query not working as it should -

-

so have 3 tables:

authors: -------- id    name 1     john 2     sue 3     mike  authors_publications: --------------------- authorid    paperid 1            1 1            2 2            2 3            1 3            2 3            3  publications: ------------- id         year 1          2004 2          2005 3          2004

i'm trying join them count number of publications each author has had on 2004. if didn't publish should zero

ideally result should this:

id    name    publications_2004  1    john           1  2    sue            0  3    mike           2

i tried following:

select a.id, name, count(*) publications_2004  authors_publications ap left join authors on ap.authorid=a.id left join publications p on p.id=ap.paperid  year=2004  group ap.authorid

i don't understand why it's not working. removing authors haven't published in 2004.

your statement taking result set returned join's , them trimming off records year<>2004.

to around can few different things

you can apply filter publications table in on statement when joining. filter results before joining

 select a.id,     name,     count(*) publications_2004 authors_publications ap left join authors     on ap.authorid = a.id left join publications p     on p.id = ap.paperid ,         p.year = 2004 group ap.authorid

you use case statement instead of where:

select a.id,     name,     sum(case when p.year = 2004 1 else 0) end publications_2004 authors_publications ap left join authors     on ap.authorid = a.id left join publications p     on p.id = ap.paperid  group ap.authorid, name

you use subquery pre-filter publications table 2004 records, explicitly doing implicit in first option:

select a.id,     name,     count(*) publications_2004 authors_publications ap left join authors     on ap.authorid = a.id left join (select * publications , year = 2004) p     on p.id = ap.paperid  group ap.authorid, name

also, because not aggregating name formula, should add group by otherwise may funky results.


Comments

Post a Comment

Popular posts from this blog

SQL php on different pages to Insert (mysqli) -

-
i have piece of php code, works when placed on index page ( index.php ), nothing if put in page, i.e. add.php for example: having code add.php page, tried go to: www.myhost.com/add.php , nothing happen. if copy code index.php , go page www.myhost.com/index.php add row in sql table. why that? <?php $server="mysql.server.com"; $user="randomusr"; $pass="randompwd"; $db="randomdb"; $link=mysqli_connect($server, $user, $pass, $db); $result=mysqli_query($link,"select * `battery` `id`"); $query = "insert battery (id,type,owner,charge,time) values ('1','lily002','maisnow','15','2015-04-22 17:20:28')"; mysqli_query($link, $query); header("location: index.php"); //this 1 comment out when copied index.php ?> l # name type collation attributes null 1 id int(16) no ...
Read more

php - How can an email be returned from Stripe Checkout? -

-
using stripe, want store customer email address email supplied in checkout. unfortunately, posting stripeemail in charge.php file returns null . how can return email checkout can use send receipt? here's form code: <script src="https://checkout.stripe.com/v2/checkout.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.js"></script> <form action="charge.php" method="post"> <input type="hidden" id="amount" name="chargeamount"/> <button data-charge-amount="300000" data-charge-name="name" data-charge-description="description">select pledge level</button> <button data-charge-amount="123123" data-charge-name="name2" data-charge-description="description2">donate</button> </form> <script> $('button').click(function(){ var token...
Read more

sql - Partition elimination in Greenplum -

-
i have scenario this: select * package package_type in ('box','card') the table partitioned package_type field. assume there twenty possible values package_type field. there twenty partitions including box , card , default partitions. when above query run, partition elimination happens correctly , box , card partitions scanned. result quick. however, when same query written this: select * package package_type in (select package_type package_list_table) , column package_type in package_list_table contains 2 values box , card . when above query run, 20 partitions being scanned. degrades performance. it seems compiler failing identify second query correctly , result partitions getting accessed. any workarounds overcome this? thanks in advance. the postgres manual page on partitioning includes caveat constraint exclusion works when query's clause contains constants (or externally supplied parameters). example, comparison agains...
Read more