November 18, 2011 at 4:31 am
Hi All,
I am trying to do some calculation on my tables but i need your advice about the best way to do it.
I have large table such as the following values: notice frequent means count for the the records with same values.
A1 | A2| A3 | A4 | A5 | frequent
m | f | aaa | 10 | 3 | 1
m | f | aaa | 5 | 2 | 1
s | m | bbb | 10 | 3 | 1
i divide the above table to two small tables and group by the values to tableA and Table B
INSERT TableA (A1,A2,A3,frequent)
SELECT A1,A2,A3,COUNT(*)
FROM mastertable
GROUP BY A1,A2,A3
ex
TableA
A1 | A2 | A3 | frequent
m | f | aaa | 2
s | m | bbb | 1
INSERT TableB (A4,A5,frequent)
SELECT A4,A5,COUNT(*)
FROM mastertable
GROUP BY A4,A5
ex
Table B
A4 | A5 | frequent
10 | 3 | 2
5 | 2 | 1
I would like to get the result of dividing the frequent of table B on the frequent of Table A, where values in table A and values in Table B must be exist in Large table.
Ex. count_row_tableB/ count_ row_tableA) where attributes values for tableA and tableB must be exist in master table.
Thank you very much in advance for your help.
November 18, 2011 at 5:48 am
I'm having a hard time trying to understand the logic... could you please post the expected result as well as how are you planning to match rows on TableA and TableB?
In regards to "attributes must exist" on MasterTable... that's a given since TableA and TableB are derived from MasterTable therefore attributes exist there by design.
_____________________________________
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.November 18, 2011 at 6:25 am
Do a join from TableA to the master table, then from that to TableB. Select Distinct on just the columns from TableA and TableB in that. That will give you the rows in B that match the rows in A. Then you can divide the two frequency columns easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply