Group and count Problem

[Raul2005]

I need some help to modify the following query

QUERY I

Select distinct
trunc(sessions.starttime) as date,
comp.CompanyID,
comp.HC,
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
group by
date,sessions.userid,comp.CompanyID,comp.HC,sessions.userid


OUTPUT FROM QUERY I

Date companyID HC userid
2005-11-29 A000 107 10001
2005-11-29 A000 107 10001
2005-11-29 D000 107 20000

2005-11-29 c000 null 3000
2005-11-29 c000 null 3000

2005-8-26 B000 null 0002
2005-8-26 B000 null 0003
2005-8-26 B000 null 0003


If the field HC is not equal Null then this company will be considered as DEALER in the other case
the company WILL BE CONSIDERED AS CLIENT.
Distinct has to be apply if CompanyID has the same date. Also Distinct needs to be apply for userid in case
a usersid has the same date and company.

The final result should be like this...
Results

StartTime Cliente Dealer USERIDS
2005-11-29 1 2 3
2005-8-29 1 0 2

Any help will be apreciate it.

 

[r937]

select date as StartTime
     , sum(case when HC is Null 
                then 0 else 1 end)
            as  Client
     , sum(case when HC is Null 
                then 1 else 0 end)
            as  Dealer
     , count(distinct userid)
            as USERIDS
  from (
       [i]paste your query here[/i]
       ) as q
group
    by date

r937.com | rudy.ca

 

[gmmastros]

r937, I don't think your results are correct because your 'Sum(Case' does not accomodate duplicates in the data.

This is a tough problem because the grouping causes duplicate items that need to be counted (as though those items are unique). Duplicate records in the table don't help either. Of course, I wouldn't be saying all this if I didn't come up with a 'potential' solution myself. Here it is...

Declare @Output Table(Date DateTime, CompanyId VarChar(10), HC Integer, UserId Integer)

insert into @Output Values('2005-11-29','A000',107 ,10001)
insert into @Output Values('2005-11-29','A000',107 ,10001)
insert into @Output Values('2005-11-29','D000',107 ,20000)
insert into @Output Values('2005-11-29','c000',null,3000)
insert into @Output Values('2005-11-29','c000',null,3000)
insert into @Output Values('2005-8-26' ,'B000',null,0002)
insert into @Output Values('2005-8-26' ,'B000',null,0003)
insert into @Output Values('2005-8-26' ,'B000',null,0003)
[green]-- 2 more rows added to make sure the data is right[/green]
insert into @Output Values('2005-8-27' ,'B000',202,0002)
insert into @Output Values('2005-8-27' ,'B000',202,0003)
		
Select	Users.Date,
		IsNull(Client, 0) As Client,
		IsNull(Dealer, 0) As Dealer,
		UserIds
From	(
		Select 	Date,
				Count(Distinct UserId) As UserIds
		From	@Output
		Group By Date
		) Users
		Left Join	(
					Select	Date,
							Count(CompanyId) As Client
					From	(Select Distinct 
									Date, 
									CompanyId, 
									HC 
							From @Output) as q
					Where	HC Is NULL
					Group By Date
					) Clients On Users.Date = Clients.Date
		Left Join 
					(
					Select	Date,
							Count(CompanyId) As Dealer
					From	(Select Distinct 
									Date, 
									CompanyId, 
									HC 
							From @Output) as q
					Where	HC Is Not NULL
					Group By Date
					) Dealers  On Users.Date = Dealers.Date
Order By Users.Date DESC

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Hm... I'm curious about how Query 1 returns duplicate rows since DISTINCT is used.

Plus combination of DISTINCT and GROUP BY is odd at best.

And sessions.userid is used twice in GROUP BY...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

nicely spotted, vongrunt

i would've gone into those details too, but in this case i deemed it easier (on me, i guess) just to give a solution that i know should work

r937.com | rudy.ca