Looking for some help writing a SQL query

[mmrmnhrm]

Have a big-ass database I'm trying to pull some info out of, which exists in two tables. Most of what I need is in the first, does not require any special coding, and I've got that running ok. However, there are three pieces of info which live in the second which I need to tally up based on their groupID value, then append to the results, followed by a pair of zeros. A full result looks like this:

 

Tanoo,30000001,20000001,-8.85107925999806e16,4.23694439668789e16,-4.45135253464797e16,0.858324068848468,6,7,4,0,0

(system name, system id, constellation id, x-y-z coordinates, security level, # planets, # moons, # belts, 0, 0)

 

"System ID" is a primary key which exists in both tables. In the second table, all planets are groupID=7, moons are groupID=8, and belts groupID=9. The bold portion is being retrieved with this query:

USE [ebs_DATADUMP]

GO

 

SELECT [solarSystemName]

,[solarSystemID]

,[constellationID]

,[x]

,[y]

,[z]

,[security]

FROM [dbo].[mapSolarSystems]

GO

Now I just need some help getting that last little underlined piece, which lives in [dbo].[mapDenormalize]

[Wease]

By "System ID" are you referring to the "solarSystemID"?

[nismopc]

Without having SQL and database in front of me, can't verify this, but I "think" this should do it as long as all the columns from table A are distinct to the System Id...

 

SELECT

A.[solarSystemName]

,A.[solarSystemID]

,A.[constellationID]

,A.[x]

,A.[y]

,A.[z]

,A.[security]

,SUM(CASE WHEN B.[Planets] > 0 THEN 1 ELSE 0 END) as [Num_Planets]

,SUM(CASE WHEN B.[Moons] > 0 THEN 1 ELSE 0 END) as [Num_Moons]

,SUM(CASE WHEN B.[belts] > 0 THEN 1 ELSE 0 END) as [Num_Belts]

,0 as [Zero_1]

,0 as [Zero_2]

FROM [dbo].[mapSolarSystems] A

JOIN [dbo].[OtherTableName] B

ON A.[solarSystemID] = B.[solarSystemID]

GROUP BY

A.[solarSystemName]

,A.[solarSystemID]

,A.[constellationID]

,A.[x]

,A.[y]

,A.[z]

GO

[nurkvinny]

You want a JOIN statement.

 

http://en.wikipedia.org/wiki/Join_%28SQL%29

 

http://www.w3schools.com/sql/sql_join.asp

 

Doh, he beat me by a couple minutes while I was looking for some good links.

[mmrmnhrm]

Sean: Yes

Brandon: Your code makes sense to me, but perhaps I'm not copying it correctly (though this being the first time I've ever tried to use SQL, I might be missing something obvious)

USE [ebs_DATADUMP]

GO

 

SELECT A.[solarSystemName]

,A.[solarSystemID]

,A.[constellationID]

,A.[x]

,A.[y]

,A.[z]

,A.[security]

,SUM(CASE WHEN B.[Planets] > 0 THEN 1 ELSE 0 END) as [Num_Planets]

,SUM(CASE WHEN B.[Moons] > 0 THEN 1 ELSE 0 END) as [Num_Moons]

,SUM(CASE WHEN B.[belts] > 0 THEN 1 ELSE 0 END) as [Num_Belts]

,0 as [Zero_1]

,0 as [Zero_2]

FROM [dbo].[mapSolarSystems] A

JOIN [dbo].[mapDenormalize] B

ON A.[solarSystemID] = B.[solarSystemID]

GROUP BY

A.[solarSystemName]

,A.[solarSystemID]

,A.[constellationID]

,A.[x]

,A.[y]

,A.[z]

GO

(yes, I did remember to change 'JOIN [dbo].[OtherTableName] B' to 'JOIN [dbo].[mapDenormalize] B') I get the following error out:

Msg 8120, Level 16, State 1, Line 8

Column 'dbo.mapSolarSystems.security' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 207, Level 16, State 1, Line 9

Invalid column name 'Planets'.

Msg 207, Level 16, State 1, Line 10

Invalid column name 'Moons'.

Msg 207, Level 16, State 1, Line 11

Invalid column name 'Belts'.

Vince: Was pretty sure JOIN was a part of it, since there are two tables involved... just never done anything remotely like this before, so it's sorta like giving a toddler some wrenches and telling them to tune a Shelby

[mmrmnhrm]

Brandon... make that "I see a hole in the code" How should SQL determine whether or not there's a planet to count without a comparison against groupID=7 (same for moons/8 and belts/9)?

 

Add: The database (in MSSQL backup format) is available here, 104MB.

[nismopc]

Oops...

 

...missed a few things...

 

...try this and let me know the results.

 

SELECT A.[solarSystemName]

,A.[solarSystemID]

,A.[constellationID]

,A.[x]

,A.[y]

,A.[z]

,A.[security]

,SUM(CASE WHEN B.[GroupID] = 7 THEN 1 ELSE 0 END) as [Num_Planets]

,SUM(CASE WHEN B.[GroupID] = 8 THEN 1 ELSE 0 END) as [Num_Moons]

,SUM(CASE WHEN B.[GroupID] = 9 THEN 1 ELSE 0 END) as [Num_Belts]

,0 as [Zero_1]

,0 as [Zero_2]

FROM [dbo].[mapSolarSystems] A

JOIN [dbo].[mapDenormalize] B

ON A.[solarSystemID] = B.[solarSystemID]

GROUP BY

A.[solarSystemName]

,A.[solarSystemID]

,A.[constellationID]

,A.[x]

,A.[y]

,A.[z]

,A.[security]

GO

[mmrmnhrm]

Pure awesome, mate... all I had to do was change [GroupID] to [groupID] and the thing worked!

[nismopc]

No prob...