sql - Rewriting the HAVING clause and COUNT function -

-

i'm trying conceptually understand how rewrite having clause , count function.

i asked "find names of reviewers have contributed 3 or more ratings. (as challenge, try writing query without having or without count.)" in relation this simple database: http://sqlfiddle.com/#!5/35779/2/0

the query having , count easy. without, i'm having difficulty.

help appreciated. thank you.

one option use sum(1) in place of count in subquery, , using where instead of having:

select b.name (select rid,sum(1) sum1       rating       group rid       )a join reviewer b   on a.rid = b.rid sum1 >= 3

demo: sql fiddle

update: explanation of sum(1): adding constant select statement result in value being repeated every row returned, example:

select rid       ,1 col1 rating

returns:

| rid | col1 | |-----|------| | 201 |    1 | | 201 |    1 | | 202 |    1 | | 203 |    1 | | 203 |    1 | ......

sum(1) applying constant 1 every row , aggregating it.


Comments

Post a Comment

Popular posts from this blog

ruby on rails - RuntimeError: Circular dependency detected while autoloading constant - ActiveAdmin.register Role -

-
i using activeadmin , have file doing this: activeadmin.register role something--- end however, server gives me error: runtimeerror: circular dependency detected while autoloading constant role /library/ruby/gems/2.0.0/gems/activesupport-4.0.10/lib/active_support/dependencies.rb:461:in `load_missing_constant' /library/ruby/gems/2.0.0/gems/activesupport-4.0.10/lib/active_support/dependencies.rb:184:in `const_missing' ~/desktop/boxfox/app/admin/role.rb:1:in `<top (required)>' /library/ruby/gems/2.0.0/gems/activesupport-4.0.10/lib/active_support/dependencies.rb:424:in `load' /library/ruby/gems/2.0.0/gems/activesupport-4.0.10/lib/active_support/dependencies.rb:424:in `block in load_file' /library/ruby/gems/2.0.0/gems/activesupport-4.0.10/lib/active_support/dependencies.rb:616:in `new_constants_in' /library/ruby/gems/2.0.0/gems/activesupport-4.0.10/lib/active_support/dependencies.rb:423:in `load_file' /library/ruby/gems/2.0.0/gems/activesuppor...
Read more

c++ - OpenMP unpredictable overhead -

-
i have simple openmp code runs 4 empty threads , reports time: #include "omp.h" #include <sys/time.h> #include <cstdio> double start_time; void process_thread() { } int main(){ struct timeval tv; gettimeofday(&tv, null); start_time = 1000*((double)tv.tv_sec + 1e-6*(double)tv.tv_usec); printf("beginning time: %.3fms ",start_time-start_time); #pragma omp parallel schedule(static,1) for(int i=0;i<4;i++) process_thread(); gettimeofday(&tv, null); double cur_time = 1000*((double)tv.tv_sec + 1e-6*(double)tv.tv_usec); printf("finishing time: %.3fms\n\n",cur_time-start_time); } without openmp running time small , consistent: beginning time: 0.000ms finishing time: 0.050ms beginning time: 0.000ms finishing time: 0.050ms beginning time: 0.000ms finishing time: 0.049ms beginning time: 0.000ms finishing time: 0.049ms beginning time: 0.000ms finishing time: 0.050ms beginnin...
Read more

python - ValueError when using pandas.read_json -

-
i made 250mb json file should this: [ {"a":"uniquevalue0", "b":[1,2,3]}, {"a":"uniquevalue1", "b":[1]}, {"a":"uniquevalue2", "b":[1,2,3,4]} ] where "b" value can variable len >= 1. this says have valid json. i call df = pandas.read_json('ut1.json', orient = 'records', dtype={"a":str, "b":list}) here documentation. when reading pandas dataframe, following traceback: traceback (most recent call last): file "<stdin>", line 1, in <module> file "/.../pandas/io/json.py", line 198, in read_json date_unit).parse() file "/.../pandas/io/json.py", line 266, in parse self._parse_no_numpy() file "/.../pandas/io/json.py", line 496, in _parse_no_numpy loads(json, precise_float=self.precise_float), dtype=none) valueerror: unexpected character found when decoding ...
Read more