SELECT statement to group - 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 2005 Forums

Transact-SQL (2005)

SELECT statement to group

Author

Topic

shasha
Starting Member

13 Posts

Posted - 2008-07-03 : 09:52:36

Hi, sorry to take anyone’s time on this but I am currently out in DC with the "big guys" and one happened to be asked a SQL question and I am hoping one of you can help... :)

Okay he has a very simple db "SQL serv 2005" and has one PK "index" it numbers every colume..1,2,3,4..ect.. has no FK. so the schema is super simple and looks like this..

INDEX (PK) Track Number Time (hour only)
1 1 4
2 1 4
3 2 6
4 2 7

okay, right now through what I am assuming is a select statement he is getting these results
At 4, two tracks. At 6 one track. At 7 one track.

What he wants is:
At 4 one unique track. Though two show they were in fact one event. These are photos of the same taken of some event. So at four two photos were shot for a man walking. My boss would like a simple query that show them unique and not separate instances...

I hope this wasn't too confusing..thank you so much for your help!!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-03 : 09:56:51

i am a bit confused by the way you've described it and it perhaps b better to illustrate what you mean with a smaple resultset?

but maybe you just want...

select time, count(distinct track_column)
from yourTable
group by time

Em

shasha
Starting Member

13 Posts

Posted - 2008-07-03 : 10:00:12

thanks..for some reason when I post it moved all the numbers under index??? Thanks, I will try that..
just in case, let me try again :)
Index Track Time (hour)
1 1 4
2 1 4
3 2 5
4 2 6

thanks for your help on this Em!!

Shannon

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-03 : 10:01:39

you can use code tags to help get your data to line up etc. [ code ] and [ / code ] (without the spaces)

of more interest to us would be the sample result though?

Em

shasha
Starting Member

13 Posts

Posted - 2008-07-03 : 10:08:29

I would like the output to look like this..

4 | 1
5 | 1
6 | 1

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-03 : 10:09:24

then i think the count distinct i gave you is what you want

Em

shasha
Starting Member

13 Posts

Posted - 2008-07-03 : 10:14:51

I trust you... :) odds are he doesnt even know what he wants...but you rock for making me look like I know a little..

thanks