complex query

[twcman]

I need to build a query. I have a table with user login log info. every time a user logs in, an entry with sessionstart(activity_id) and user_name is entered in the table. I need a query that counts the number of times each user has logged in. This is the sql that I have so far:

select count(*)activity_id from event_log where activity_id=3 and user_name=(select distinct(user_name) from event_log)

Chris

Chris Scott
The Whole Computer Medical Systems

 

[philhege]

Hi Chris,

SELECT COUNT(activity_id), user_name from event_log
where activity_id = 3
group by user_name

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[TruthInSatire]

slight mod.

SELECT COUNT(activity_id)

should be

SELECT COUNT(activity_id) [COLOR=red]as someNameYouWantToUse[/color]

Beware of programmers who carry screwdrivers.

 

[philhege]

Zactamundo, else it'd be called COMPUTED_COLUMN_1 or some such nonsense.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[mitzi2]

I have a table with names and addresses of people. There are many people from the same city. I need to take only one from NYC, only one from Chicago, only one from LA etc. I don’t care who are the ones who would not be selected. DISTINCT would not resolve my problem. Currently I am doing it programmatically with a short SP using cursor. I am confident that there is a better way to accomplish it. Can someone help me with this?

 

[r937]

mitzi2, we'd be happy to, please start a new thread and show your current query

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)