CODEWITHSUNDEEP
mysqlsqloracle-database
kevinh
kevinh

Reputation: 59

Oracle SQL - Writing a join with count query

I am currently working on logging the number of users in a database that have logged in during the last year.

This is the example schema I am working with: http://sqlfiddle.com/#!4/7dfb6/15 (copied below)

I have these two queries:

Query 1:

select distinct userid from tosuser tu;

Query 2:

select count (*) from tosevent te where te.LASTUPDATEDATE BETWEEN 
to_date('01/01/2014', 'dd/mm/yyyy') AND to_date('31/12/2014', 'dd/mm/yyyy');

That I was hoping to join, to give a result like this:

USERNAME    COUNT
FRED         7
MARY         2

Where count refers to the number of logins the user made in the last year. (I am currently passing the year as a java parameter)

The closest thing I have working is this: 

select distinct username, LASTUPDATEDATE from tosuser tu, tosevent te where 
te.LASTUPDATEDATE BETWEEN 
to_date('01/01/2014', 'dd/mm/yyyy') AND to_date('31/12/2014', 'dd/mm/yyyy')
and te.username = tu.userid and tu.userenabled='1' and tu.userid!='OASIS';

Which gives me each login event. Was just wondering if you know how I would write this query using Oracle syntax?

Schema from SQL fiddle if that dissapears:

CREATE TABLE tosuser ( userid     VARCHAR2(30) PRIMARY KEY, IsDeleted    INT NOT NULL, UserEnabled    INT,ExternalUser  INT);

CREATE TABLE tosevent ( id      VARCHAR2(30) PRIMARY KEY, username VARCHAR2(30), DATA3 VARCHAR2(30), LASTUPDATEDATE DATE  );

INSERT into tosuser (userid, IsDeleted, UserEnabled, ExternalUser) values ('OASIS',1,1,1);    
INSERT into tosuser (userid, IsDeleted, UserEnabled, ExternalUser) values ('FRED',1,1,1);
INSERT into tosuser (userid, IsDeleted, UserEnabled, ExternalUser) values ('MARY',1,1,1);
INSERT into tosuser (userid, IsDeleted, UserEnabled, ExternalUser) values ('TOM',1,1,1);
INSERT into tosuser (userid, IsDeleted, UserEnabled, ExternalUser) values ('MICHEAL',1,1,1);

INSERT into tosevent (id, username, DATA3, LASTUPDATEDATE) values (1,'FRED','successful logon',to_date('08/09/2014', 'dd/mm/yyyy'));
INSERT into tosevent (id, username, DATA3, LASTUPDATEDATE) values (2,'FRED','successful logon',to_date('09/09/2014', 'dd/mm/yyyy'));
INSERT into tosevent (id, username, DATA3, LASTUPDATEDATE) values (3,'FRED','successful logon',to_date('10/09/2014', 'dd/mm/yyyy'));
INSERT into tosevent (id, username, DATA3, LASTUPDATEDATE) values (4,'FRED','successful logon',to_date('11/09/2014', 'dd/mm/yyyy'));
INSERT into tosevent (id, username, DATA3, LASTUPDATEDATE) values (5,'FRED','successful logon',to_date('12/09/2014', 'dd/mm/yyyy'));
INSERT into tosevent (id, username, DATA3, LASTUPDATEDATE) values (6,'MARY','successful logon',to_date('08/09/2014', 'dd/mm/yyyy'));
INSERT into tosevent (id, username, DATA3, LASTUPDATEDATE) values (7,'MARY','successful logon',to_date('09/09/2014', 'dd/mm/yyyy'));

Thanks.

Upvotes: 0

Views: 93

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I think you just want a group by:

select username, count(*)
from tosuser tu join
     tosevent te 
     on te.username = tu.userid
where te.LASTUPDATEDATE BETWEEN to_date('01/01/2014', 'dd/mm/yyyy') AND
                                to_date('31/12/2014', 'dd/mm/yyyy') and
      tu.userenabled = '1' and tu.userid <> 'OASIS'
group by username;

You need to be careful using dates. If the LastUpdateDate has a time component, then you want to phrase this as:

where te.LASTUPDATEDATE >= date '2014-01-01' and te.LASTUPDATEDATE < date '2015-01-01'

You can also write this just by looking at the year:

where extract(year from LASTUPDATEDATE) = 2014

However, this version may not use indexes as effectively as the previous version.

Upvotes: 3

gpeche
gpeche

Reputation: 22524

You cannot use DISTINCT that way, because it applies to ALL fields in the SELECT, not just to the following one. You should be using GROUP BY:

select username, LASTUPDATEDATE
from tosuser tu, tosevent te 
where 
te.LASTUPDATEDATE BETWEEN to_date('01/01/2014', 'dd/mm/yyyy') AND to_date('31/12/2014', 'dd/mm/yyyy')
and te.username = tu.userid 
and tu.userenabled='1' 
and tu.userid!='OASIS'
GROUP BY username

As a somewhat related comment, if you ever see something like:

select distinct(foo), bar ...

then you should review that query as it is probably not doing what it is meant to do. That SELECT means the same as

select distinct foo, bar ...

Upvotes: 1

Related Questions