Noob needs help figuring out a query

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

General SQL Server Forums

New to SQL Server Programming

Noob needs help figuring out a query

Author

Topic

KidCactus
Starting Member

3 Posts

Posted - 2012-09-06 : 13:23:52

Hi guys. I'm very new to sql and I'm stuck on what I assume should be a quite easy query...

If I have a table that looks something like this:

time	        		name		data_1		data_2

2012-01-01 00:01		id_1		1111		2222
2012-01-01 00:01		id_1		1111		2222
2012-01-01 00:02		id_2		1111		2222
2012-01-01 00:03		id_1		1111		2222
2012-01-01 00:03		id_1		1111		2222
2012-01-01 00:03		id_3		1111		2222
2012-01-01 00:03		id_2		1111		2222
2012-01-01 00:03		id_1		1111		2222
2012-01-01 00:04		id_4		1111		2222
2012-01-01 00:04		id_1		1111		2222
2012-01-01 00:04		id_1		1111		2222
2012-01-01 00:05		id_2		1111		2222
2012-01-01 00:06		id_2		1111		2222
2012-01-01 00:06		id_2		1111		2222
2012-01-01 00:06		id_4		1111		2222
2012-01-01 00:07		id_3		1111		2222
2012-01-01 00:07		id_1		1111		2222
2012-01-01 00:07		id_1		1111		2222
2012-01-01 00:08		id_3		1111		2222
2012-01-01 00:08		id_3		1111		2222

How would a query look like, that show this is a result:

time        			name		counted

2012-01-01 00:01		id_1		2
2012-01-01 00:02		id_2		1
2012-01-01 00:03		id_1		3
2012-01-01 00:03		id_3		1
2012-01-01 00:03		id_2		1
2012-01-01 00:04		id_4		1
2012-01-01 00:04		id_1		2
2012-01-01 00:05		id_2		1
2012-01-01 00:06		id_2		2
2012-01-01 00:06		id_4		1
2012-01-01 00:07		id_3		1
2012-01-01 00:07		id_1		2
2012-01-01 00:08		id_3		2

For every minute a certain name appears, it should be counted how many times during that minute. All I have is this so far:

select time, name
from my_table

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 13:27:38

Reading through your data (without actually testing it), you should be able to use a simple count with a group by the time and name columns:

SELECT
	[time],
	[name],
	COUNT(*)
FROM
	YourTable
GROUP BY
	[time],
	[name];

KidCactus
Starting Member

3 Posts

Posted - 2012-09-06 : 13:31:54

Thank you, yes I think that did the trick. Much appreciated! :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 13:44:58

You are welcome, glad to help!

xploddbash
Starting Member

2 Posts

Posted - 2012-09-06 : 19:13:08

sunitabeck, your answer is what I've needed. Thank you for your reply and kidcactus, i appreciate that you've posted this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources