count distinct question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

count distinct question

Author

Topic

jrowland
Starting Member

2 Posts

Posted - 2006-04-05 : 03:16:58

I'm trying to delve into "more complex" queries, but this one is stumping me.

I have 2 tables, "Aircraft" and "Models" with the following fields:
a.model (pri key on m.id)
a.registration
a.owner
a.location
....and....
m.id
m.make
m.model

I want to count the number of distinct make/model, and I also want the number of model numbers where the owner is not 0

Result would look like:
Make/Model -- Total Number -- Total Not Owned by 0
Cessna 172 -- 1,230 -- 412
Cessna 182 -- 816 -- 489

I was able to sucessfully pull the number of Make/Model with the following query, but cannot figure out how to work in the third result set.
select concat(m.make, " ",m.model) as Airframe, count(a.model) as "Total Number"
from aircraft a
join models m on a.model=m.id
group by m.id

Apreciate any assistance.

Thanks,
Jimmy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 04:21:52

you can try using sub query
(select count(*) from Aircraft x where x.id = m.id and x.owner <> 0)

jrowland
Starting Member

2 Posts

Posted - 2006-04-05 : 23:05:08

Wow, I guess I was making it harder than it needed to be. Now, I am trying to throw a third number into the mix, how many times each make/model gets used.

My third table, in addition to the 2 above is "logs"
the fields are:
logs.aircraft = registration number pulled in from aircraft.registration
logs.user is for the name of the person using the plane

I'm trying the below query, but I'm getting 11,568 for every make/model, so obviously I'm not getting distinct results.

select concat(m.make, " ",m.model) as Airframe,
(select count(*)
FROM logs l join aircraft x on x.registration=l.aircraft
where x.model = m.id and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= `time`
) as "#TimesRented"
from aircraft a
join models m on a.model=m.id
group by m.id

Again, thanks.
Jimmy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources