SQL query help please

Question

Post reply

SQL query help please

ludovic_sky

Grasshopper

Points: 18
More actions
April 27, 2010 at 6:38 am

#146753

Hi,
im trying to do a group by in this data warehousing for my star schema but i cant seem to do it properly
SELECT dbo.DimAge.Age, dbo.DimFnlwgt.fnlwgt, dbo.DimRace.Race, dbo.DimWorkClass.WorkClass, dbo.DimSex.Sex,
dbo.DimHoursPerWeek.Hours_per_week, dbo.DimCountry.Country, dbo.DimEducation.Education_num, dbo.DimOccupation.Occupation,
dbo.DimTaxDetails.Capital_gain
FROM dbo.DimAge CROSS JOIN
dbo.DimCountry CROSS JOIN
dbo.DimEducation CROSS JOIN
dbo.DimFnlwgt CROSS JOIN
dbo.DimHoursPerWeek CROSS JOIN
dbo.DimOccupation CROSS JOIN
dbo.DimRace CROSS JOIN
dbo.DimSex CROSS JOIN
dbo.DimTaxDetails CROSS JOIN
dbo.DimWorkClass
GROUP BY DimCountry.Age, DimFnlwgt.fnlwgt, DimRace.Race, DimWorkClass.WorkClass, DimTaxDetails.Sex, DimHoursPerWeek.Hours_per_week,
DimCountry.Country, DimEducation.Education_num, DimRace.Occupation, DimTaxDetails.Capital_gain
but its not working can anyone help out please?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

where... where is the WHERE clause? 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ludovic_sky Grasshopper Points: 18 More actions · Answer 2

i thought u dont need to the WHERE clause because its using the joining methods already??

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

I guess Paul is asking where is the problem?

---------------------------------------------------------------------------------

Cory E. SSCertifiable Points: 7076 More actions · Answer 4

what is the error you are getting? "its not working" seems a bit vague.

-- Cory

LutzM SSC Guru Points: 107049 More actions · Answer 5

You're referencing different tables in your SELECT and GROUP BY clause:

SELECT

dbo.DimAge.Age,

dbo.DimSex.Sex,

dbo.DimOccupation.Occupation

GROUP BY

DimCountry.Age,

DimTaxDetails.Sex,

DimRace.Occupation

Since we're talking about a cross join across 10 tables: I truly hope your tables will just hold a lower single digit number of rows each... Otherwise it's going to be a rather huge table to select from... :pinch:

I strongly recommend to think about those joins if it's really what you're looking for...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 6

More details are definitely needed on the whole "not working out" statement.

Given your statement about JOINS taking care of the recordset and not needing a WHERE clause makes me think you A) don't understand JOINS very well and B) have no idea what a CROSS JOIN does to your result set. I suggest you look up JOIN in Books Online and read very carefully.

For the record, the only JOIN statement that even partially filters the total record set would be an INNER JOIN and even that's not a guarantee of cutting down your records depending on how your data is set up.

Repeat after me: Cross Joins are EVIL. Cross Joins are EVIL. I will never use another Cross Join again because they are EVIL.

@=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.