Special Select Statement with comparison

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

Special Select Statement with comparison

Author

Topic

exaphi
Starting Member

4 Posts

Posted - 2012-02-06 : 15:17:15

Hello together,

I have a Table with three column: timestamp, id and value

Now I want to create a select Statement with the following tasks:

Summe each value if timestamp is equal and id IN (1, 2, 3) for example

So the feedback of the Statement give back the column timestamp and value.

Are there any Ideas?
I´m sorry for my english ;O)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-06 : 15:21:26

[code]SELECT
[timestamp],
SUM([value])
FROM
YourTable
WHERE
id IN (1,2,3)
GROUP BY
[timestamp];[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 15:23:58

or is it?


SELECT
	[timestamp],
	SUM([value])
FROM
	YourTable
WHERE
	id IN (1,2,3)
GROUP BY
	[timestamp];
HAVING COUNT(DISTINCT id)=3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

exaphi
Starting Member

4 Posts

Posted - 2012-02-08 : 08:16:23

Great Solution!!

Thank you both!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 13:53:19

whicj one you were looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

exaphi
Starting Member

4 Posts

Posted - 2012-02-09 : 09:01:45

@visakh16
I use your solution and it works great!

But I have another question:

I want to get the sum in a timerange of one hour.

I´m thinking about it and my solution ist:


SELECT
	Convert(nchar(13), timestamp, 126)+ ':00:00',
	SUM([value])
FROM
	YourTable
WHERE
	id IN (1,2,3)
GROUP BY
	Convert(nchar(13), timestamp, 126)

HAVING COUNT(DISTINCT id)=3

What do you thinking about that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 12:43:27

better way is


SELECT
	DATEADD(hh,DATEDIFF(hh,0,[timestamp]),0) AS HourDate,
	SUM([value])
FROM
	YourTable
WHERE
	id IN (1,2,3)
GROUP BY
	DATEADD(hh,DATEDIFF(hh,0,[timestamp]),0)
HAVING COUNT(DISTINCT id)=3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

exaphi
Starting Member

4 Posts

Posted - 2012-02-15 : 06:32:14

Wie wäre es denn mit:


SELECT
	DATEADD(hh,DATEDIFF(hh,0,[timestamp]),0) AS HourDate,
	SUM([value])
FROM
	YourTable
WHERE
	id IN (1,2,3)
GROUP BY
	Datename(hh, [timestamp]

HAVING COUNT(DISTINCT id)=3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 09:17:45

quote:

Originally posted by exaphi

Wie wäre es denn mit:


SELECT
	DATEADD(hh,DATEDIFF(hh,0,[timestamp]),0) AS HourDate,
	SUM([value])
FROM
	YourTable
WHERE
	id IN (1,2,3)
GROUP BY
	Datename(hh, [timestamp]

HAVING COUNT(DISTINCT id)=3

did you try compiling this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources