sql - Getting rows with the highest SELECT COUNT from groups within a resultset -


i have sqlite database contains parsed apache log lines.

a simplified version of db's table (accesses) looks this:

|referrer|datestamp| +--------+---------+ |xy.de   | 20170414| |ab.at   | 20170414| |xy.de   | 20170414| |xy.de   | 20170414| |12.com  | 20170413| |12.com  | 20170413| |xy.de   | 20170413| |12.com  | 20170413| |12.com  | 20170412| |xy.de   | 20170412| |12.com  | 20170412| |12.com  | 20170412| |ab.at   | 20170412| |ab.at   | 20170412| |12.com  | 20170412| +--------+---------+

i trying retrieve top referrer each day performing sub query select count on referrer. afterwards select entries subquery have highest count:

select datestamp, referrer, count(*) accesses datestamp between '20170414' , '20170414' group referrer having count(*) = (select max(anz)                     (select count(*) anz                           accesses                          datestamp between '20170414' , '20170414'                          group referrer                         )                   );

the above approach works long perform query single date, falls apart query date ranges. how can achieve grouping date? interested in referrer highest count.

if want days combined single best referrer, then:

select referrer, count(*) anz  accesses datestamp between '20170414' , '20170414' group referrer order count(*) desc limit 1;

i think might want information broken out day. if so, correlated subquery helps -- , cte well:

with dr (       select a.datestamp, a.referrer, count(*) cnt       accesses       datestamp between '20170414' , '20170414'       group a.referrer, a.datestamp      ) select dr.* dr dr.cnt = (select max(dr2.cnt)                 dr dr2                 dr2.datestamp = dr.datestamp                );

-

Comments

Post a Comment

Popular posts from this blog

cookies - Yii2 Advanced - Share session between frontend and mainsite (duplicate of frontend for www) -

i have custom members system have made: yii2 members system . can refer full details, or install can work question. i have frontend , backend yii2 provides, few modifications separate sessions/cookies backend works admin model , pulls admin table. similar old traditional member systems. mainsite clone of frontend , it's role main website. when go www.site.com or site.com . here 3 apps , example domains: mainsite = www.site.com or site.com frontend = users.site.com backend = admin.site.com when user logs in ( users.site.com/site/login ) , go homepage ( mainsite @ www.site.com ) want know logged in , show username. how frontend operates default advanced app. from have far, login , head mainsite , reads an internal server error occurred. . doesn't yii error, server error? when in yii logs under runtime, mentions access control: 2017-04-14 13:38:25 [127.0.0.1][1][-][error][yii\web\httpexception:403] exception 'yii\web\forbiddenhttpexception'...
Read more

angular - password and confirm password field validation angular2 reactive forms -

i need check whether password , confirm password fields have same value using reactive form angular2. did see lot of answers on same here, angular 2 form validating repeat password , comparing fields in validator angular2 , none seemed work me.can please help."this" undefined in validate function :( . sharing code, this.addedituserform = this.builder.group({ firstname: ['', validators.required], lastname: ['', validators.required], title: ['', validators.required], email: ['', validators.required], password: ['', validators.required], confirmpass: ['', [validators.required, this.validatepasswordconfirmation]] }); validatepasswordconfirmation(group: formgroup): any{ let valid = true; // if (this.addedituserform.controls.password != this.addedituserform.controls.confirmpass) { // valid = false; // this.add...
Read more

php - Permission denied. Laravel linux server -

when try register , send email user got error. working till yesterday. today gives me , cant figure out why. way on local testing work's. i'm stuck. please errorexception in compiled.php line 7512: file_put_contents(/var/www/html/storage/framework/views/53de219dee4f11a2db4f64a2c574ad02db5613f4.php): failed open stream: permission denied in compiled.php line 7512 @ handleexceptions->handleerror('2', 'file_put_contents(/var/www/html/storage/framework/views/53de219dee4f11a2db4f64a2c574ad02db5613f4.php): failed open stream: permission denied', '/var/www/html/bootstrap/cache/compiled.php', '7512', array('path' => '/var/www/html/storage/framework/views/53de219dee4f11a2db4f64a2c574ad02db5613f4.php', 'contents' => 'please activate account clicking on following link. <a href="<?php echo e(route('auth.activate', $token)); ?>"><?php echo e(route('auth.activate', $token)); ?...
Read more