Calculation on with group by - 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

General SQL Server Forums

New to SQL Server Programming

Calculation on with group by

Author

Topic

Malik
Starting Member

3 Posts

Posted - 2011-11-18 : 00:42:46

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 with 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 devide the above table to two small tables and group by the values TableA and Table B

TableA

A1 | A2 | A3 | frequent
m | f | aaa | 2
s | m | bbb | 1

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.

Thank you very much in advance for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 01:10:39

Post the DDL of the Table..forget the table split..why you would do that? Nevermind

Post the DDL in DML Format

INSERT INTO
SELET

and what the expected results are...

Please

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 01:55:42

if tables are not already existing ,use


SELECT A1,A2,A3,COUNT(*) AS frequent INTO TableA
FROM mastertable
GROUP BY A1,A2,A3

SELECT A4,A5,COUNT(*) AS frequent INTO TableB
FROM mastertable
GROUP BY A4,A5


and if they're already existing use

INSERT TableA (A1,A2,A3,frequent)
SELECT A1,A2,A3,COUNT(*) 
FROM mastertable
GROUP BY A1,A2,A3


INSERT TableB (A4,A5,frequent)
SELECT A4,A5,COUNT(*) 
FROM mastertable
GROUP BY A4,A5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Malik
Starting Member

3 Posts

Posted - 2011-11-18 : 06:21:39

Thanks for replying.

I do not have a problem with creating and group the records in a new table.

But the problem is how to calculate the(row_tableB/row_tableA) where attributes values for tableA and tableB must be in master table.

quote:
Originally posted by visakh16

if tables are not already existing ,use

SELECT A1,A2,A3,COUNT(*) AS frequent INTO TableA
FROM mastertable
GROUP BY A1,A2,A3

SELECT A4,A5,COUNT(*) AS frequent INTO TableB
FROM mastertable
GROUP BY A4,A5

and if they're already existing use

INSERT TableA (A1,A2,A3,frequent)
SELECT A1,A2,A3,COUNT(*)
FROM mastertable
GROUP BY A1,A2,A3

INSERT TableB (A4,A5,frequent)
SELECT A4,A5,COUNT(*)
FROM mastertable
GROUP BY A4,A5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 10:28:50

ok. for that do like


SELECT m.A1,m.A2,m.A3,m.A4,m.A5,b.frequent*1.0/a.frequent  as [b/a value]
FROM Master m
JOIN tableA a
ON a.A1 = m.A1
AND a.A2 = m.A2
AND a.A3 = m.A3
JOIN tableB b
ON b.A4 = m.A4
AND b.A5 = m.A5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources