April 27, 2010 at 6:38 am
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?
April 27, 2010 at 8:01 am
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.April 27, 2010 at 8:18 am
i thought u dont need to the WHERE clause because its using the joining methods already??
April 28, 2010 at 12:57 pm
I guess Paul is asking where is the problem?
---------------------------------------------------------------------------------
April 28, 2010 at 1:03 pm
what is the error you are getting? "its not working" seems a bit vague.
-- Cory
April 28, 2010 at 1:46 pm
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...
April 29, 2010 at 11:28 am
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.
@=)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply