It Came From Outer Space - 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)

It Came From Outer Space

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-01-23 : 15:33:32

This is a simple problem, but I'm stumped. I have a set of data where I want to report on by Type totalling the report and obeservation data.

Here's the code:

--Observations of Near Earth Asteroids and Mars-Crossers
DROP TABLE [dbo].[Asteroids]
CREATE TABLE [dbo].[Asteroids]
(Number nvarchar (05) NULL,
Designation nvarchar (20) NULL,
Distance_AU decimal(5,3) NULL,
Type nvarchar (20) NULL,
Observations int NULL,
Reports int NULL)
GO
INSERT Asteroids VALUES ('887 ','Alinda ','2.484','Amor 3 ',4 ,2)
INSERT Asteroids VALUES ('1915 ','Quetzalcoatl','2.542','Amor 3 ',22,14)
INSERT Asteroids VALUES ('2608 ','Sineca ','2.503','Amor 3 ',3 ,0)
INSERT Asteroids VALUES ('4179 ','Toutatis ','2.510','Apollo 3 ',19,2)
INSERT Asteroids VALUES ('7092 ','Cadmus ','2.524','Apollo 3 ',0 ,0)
INSERT Asteroids VALUES ('7345 ','Happer ','2.450','Mars-crosser',1 ,1)
INSERT Asteroids VALUES ('8709 ','Kadlu ','2.534','Mars-crosser',2 ,0)
GO

SELECT * FROM [dbo].[Asteroids] GO

I'm looking for results to look like this:

Type Obs_TOT Rep_TOT
Amor 3 29 16
Apollo 19 2
Mars-crosser 3 1

I don't know why I can't figure this one out. Any help would be greatly appreciated!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-23 : 15:58:19

This is a simple group-by query. Try this:

select	Type,
	sum(Observations) as Obs_TOT,
	sum(Reports) as Rep_TOT
from	Asteroids
group by Type

You really need to read (or re-read) the Books Online sections on SELECT queries and Aggregate functions.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-01-23 : 16:30:28

Please feel free to hit me in the head with a hammer. Occasionally, like most folks I know, I have a brainfart. This was one of those times.

Thanks

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources