Grouping problem

[Raul2005]

Select distinct
trunc(sessions.starttime) as date,
comp.name,
comp.HC,
sessions.ctype,
count (sessions.userid)
from
S_USERSESSION sessions,
S_USER users,
S_COMPANY comp
where
sessions.userid = users.userid and
users.companyid = comp.companyid and
sessions.starttime > trunc(sysdate) - 10 AND
sessions.passwordverified='Y'
group by
date,clienttype,sessions.userid,comp.name,comp.holdingcompanyid


StartTime company HC CTYPE COUNT
2005-11-29 A 107 0 1
2005-11-29 A 107 0 1
2005-11-29 D 107 0 1

2005-11-29 c null 0 1
2005-11-29 c null 0 1


2005-8-29 B null 0 1
2005-8-29 B null 0 1
2005-8-29 B null 0 1

I would like to get this result
dealer has HC field no equal Null.


Results

StartTime Cliente Dealer COUNT
2005-11-29 3 2 5
2005-8-29 3 0 3

Thanks for any help

 

[SantaMufasa]

Raul,

Let's presume that your original query is the definition of a view, which I shall call "SOME_VIEW" in my solution, below.

select to_char(StartTime,'yyyy-mm-dd')StartTime
    ,count(decode(HC,null,'x',null))Cliente
    ,count(HC)Dealer
    ,count(HC)+ count(decode(HC,null,'x',null))Cnt
from some_view
group by startTime
/

STARTTIME     CLIENTE     DEALER        CNT
---------- ---------- ---------- ----------
2005-08-29          3          0          3
2005-11-29          2          3          5

Presuming that you fix your "STARTTIME" date field to show consistently formatted dates (i.e., all days and months are two digits), then it results in the correct output, above, which matches your original request except that my output is in the correct order.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.