How to use COUNT to get zero values with mysql? -


i read several questions issue on stackoverflow : seems count should used right joining display sums of every items, including ones summing zero.

i'm not able make case, after several hours of headache...

well, have 2 tables. first 1 called "words2", list of words. second 1 called "links2". it's linking 2 words : idword1 , idword2. (there no links linking 2 identical words)

for each word, know how many links used, if there no link.

this query :

select *, count(*) qty   (      select *     words2     left outer join links2 linksa on words2.idword = linksa.idword1       union      select *     words2     left outer join links2 linksb on words2.idword = linksb.idword2 ) tmp iduser = 3 , linktype = 'individual' group word order word

it works fine unless don't have results unused words, not displayed.

thank help!

to original query change count call count(idword1). cause count number of times idword1 not null. right counting number of rows period 1 should zero.

here's sample dataset:

words2 ------- idword ------- foo bar baz biz buzz  links2 ------- idword1 | idword2 ------- foo     | bar foo     | baz bar     | baz buzz    | foo buzz    | bar

(this dataset disregards iduser , linktype fields because original question doesn't describe how used , don't appear relevant answer.)

when run query on dataset this:

idword | idword1 | idword2 | linkcount -------------------------------------- bar    | bar     | baz     | 3 baz    | null    | null    | 2 biz    | null    | null    | 1 buzz   | buzz    | foo     | 2 foo    | foo     | bar     | 3

also note count(*) more expensive depending upon storage engine you're using. see other question details.

when change count count(idword1) this:

idword | idword1 | idword2 | linkcount -------------------------------------- bar    | bar     | baz     | 3 baz    | null    | null    | 2 biz    | null    | null    | 0 buzz   | buzz    | foo     | 2 foo    | foo     | bar     | 3

here's simpler query uses no subquery , joins words2 links2 using or statement:

select   words2.idword   -- count number of links each word   -- if there no links count() call return 0   , count(idword1) linkcount words2   left join links2     on words2.idword = links2.idword1       or words2.idword = links2.idword2 group words2.idword order words2.idword

when run on sample dataset following results:

idword | linkcount ------------------- bar    | 3 baz    | 2 biz    | 0 buzz   | 2 foo    | 3

Comments

Post a Comment

Popular posts from this blog

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

c# - SharpSVN - How to get the previous revision? -

i trying find efficient way previous revision of file text comparison using sharpsvn. using (svnclient c = new svnclient()) { c.authentication.defaultcredentials = new networkcredential( configurationmanager.appsettings.get("svnserviceusername") , configurationmanager.appsettings.get("svnservicepassword") , configurationmanager.appsettings.get("svnservicedomain") ); c.authentication.sslservertrusthandlers += new eventhandler<svnsslservertrusteventargs>(authentication_sslservertrusthandlers); collection<svnfileversioneventargs> fileversioncollection = new collection<svnfileversioneventargs>(); svnrevisionrange range = new svnrevisionrange(0, this.hooks.revision); svnfileversionsargs args = new svnfileversionsargs(); args.retrieveproperties = true; args.range = range; foreach (svnchangeitem item in log.changedpaths) { string path = this.repositorypath + it...
Read more

php cli reading files and how to fix it? -

while using php cli in ubuntu got apache web server cant read file can while using in browser. , error shows nothing , next used code testing purposes : <? $handle = fopen("testfile.txt", "r"); if ($handle) { while (($buffer = fgets($handle, 4096)) !== false) { echo $buffer; } if (!feof($handle)) { echo "error: unexpected fgets() fail\n"; } if(!is_readable($handle) { die("not readable"); } fclose($handle); } ?> how fix ? edit : after removing '@' before fopen got following error fopen(testfile.txt): failed open stream: no such file or directory in /var/www/log/ka.php on line 2 when run script through cli, uses shell's working directory, in opposition file's directory (which apache hands current directory). important you, since fopen call depends on relative path; , relative paths resolved relatively working directory, not script. to have script behave apache, eit...
Read more