Basic division in SQL - 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

Basic division in SQL

Author

Topic

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-20 : 23:58:45

I want to count the rows in two tables and then give a percentage as a result.

Something like:

SELECT Count(*) / (SELECT COUNT (*) FROM Table2) FROM Table1

Just not quite sure how to do this.

*Thanks*

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 00:24:22

[code]select (select count(*) from table1) * 100.0 / (select count(*) from table2)[/code]

KH
[spoiler]Time is always against us[/spoiler]

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-21 : 01:04:12

Worked perfect.
Now I will have to work in the JOINS and see if I can still make it fly.

*Thanks*

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-21 : 01:57:29

I'm afraid I need help in the JOIN department also.
I still want to do the COUNT percentage on the two tables (table1, table2) but I have to include table3 for 'ColNames'.
I have tried different variations of what you posted above, but no luck so far.

This is the basic JOIN statement


SELECT Table3.ColName       
FROM  Table1 
INNER JOIN
Table2 ON Table1.Column2 = Table2.Column2 
AND 
Table1.Column3 = Table2.Column3 
INNER JOIN
Table3 ON Table1.Column4 = Table3.ColName

*Thanks*

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 02:01:02

[code]SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column2 = t2.Column2
AND t1.Column3 = t2.Column3
INNER JOIN Table3 t3 ON t1.Column4 = t3.ColName
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 02:02:48

[code]I still want to do the COUNT percentage on the two tables (table1, table2) but I have to include table3 for 'ColNames'.[/code]
Think it is about time you post your table DDL (only the related columns), sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-21 : 02:04:46

Thats great, but I want to do the COUNT on both Table1 and Table2 (percentages and all) with that particular JOIN statement involving the 3 tables.

*Thanks*

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-21 : 02:06:42

Sorry, we're both typing at the same time.
Let me write a better explanation and I will get back to you.

*Thanks*

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-21 : 02:28:58

Actually only the important columns are present in this join.
The join sets a conditional statement where I can relate Table1 to Table2 to check and see how many rows are equal.
Let me hopefully give you a better example or scenario…
Table2 would be the ‘MasterQuiz’ and hold the correct contents or values for a test.
Table1 would be an individuals input for that test.
Table3 is actually a table of individual names, and it is in this join just for referencing an actual name.

There are other tables which are a masters or ‘LookUps’ for the columns in Table1, Table2; but I don’t think it is necessary to include them to do this.

I just want to count the results, give a percentage, and have a name to go with it.


SELECT Table3.ColName       
FROM  Table1 
INNER JOIN
Table2 ON Table1.Column2 = Table2.Column2 
AND 
Table1.Column3 = Table2.Column3 
INNER JOIN
Table3 ON Table1.Column4 = Table3.ColName

Thats why I was trying to combine the both of them.

*Thanks*

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 02:37:33

you want number of rows return by this query OVER total no of rows in Table1 ?

How about posting some sample data for each of the table and the required result

KH
[spoiler]Time is always against us[/spoiler]

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-21 : 03:49:00

Actually I would like table1’s count over table2’s count.
Table1 will be the individuals input.
Table2 will be the master.
I have another query that lists the total correct for the ‘Students’ (with a GROUP BY and ORDER BY etc.) and I can do another query for the count on the Master or Table2; I am just having a problem combining the two in a single SQL statement and getting the desired results.
I could do the calculation with seperate bound items on the form, but I wanted to be able to this all within a single SQL statement; which this one has really thrown me.


Table1
AutoInc	Col2	Col3	Col4
00001    22	 45	Sam
00002	 23	 50	Harry
00003	 20	 40	Sue
00004	 15	 40	Tom

Table2
AutoInc	Col2	Col3
00001	 20	 40

Table3
AutoInc	        ColName
00001		Sam
00002		Harry
00003		Sue
00004		Tome

The results would go something like this: DESC

Sue	100
Tom	 50
Sam	  0
Harry	  0

*Thanks*

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 03:58:18

Sorry, can you explain how to obtain the result ? like Sue is 100, Tom is 50 ?

Also what is Table2 ? How is it related to Table1 or Table3 ?

KH
[spoiler]Time is always against us[/spoiler]

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-21 : 05:14:49

Whoops
I got too busy rapped up in the example and really didn't post the results right.
That was my fault.
Egads. Its getting late here... and I am getting 'loopy'

The results should have been:


Sue     100
Tom       0
Sam       0
Harry     0

Yes, Sue should be 100, and the rest of the people should actually have a '0' because they didn't match all of the columns in the join.
Thats why there is a need for a COUNT.
The rows have to match exactly (thats the reason for the joins).
Maybe that wasn't the best scenario above, but I need to count the total rows of each person that match exactly and compare that COUNT to the total of all of table2's rows COUNT to get a percentage.
Initially I thought I needed a count for that JOIN statement and then divide that value with a subquery that would have a count of the total rows in Table2 and display it as percent.

Anyway...
Table1 contains rows that are of the Individuals choice.
Table2 contains rows that are saved as a 'Master' to which Table1 rows are compared too.
Table1 and Table2's col2 and col3 values are linked to another table Table4. These values off of Table4 can be anything.
An individual has to match all of his row entries in Table1 to row entries for Table2.
The chances of that happening 100% of the time are not very good.
Table3 only stores the individuals Name where each of the rows of Table1 are linked.
So in that above query, only Table3 and Table1 are linked.

You know I could have just referenced Table2's primary key and used that from Table1; but I didn't.

I sincerely hope this makes sense and I haven't messed this up too bad.

*Thanks*

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 10:04:18

[code]DECLARE @Table1 TABLE
(
AutoInc varchar(5),
Col2 int,
Col3 int,
Col4 varchar(5)
)
INSERT INTO @Table1
SELECT '00001', 22, 45, 'Sam' UNION ALL
SELECT '00002', 23, 50, 'Harry' UNION ALL
SELECT '00003', 20, 40, 'Sue' UNION ALL
SELECT '00004', 15, 40, 'Tom'

DECLARE @Table2 TABLE
(
AutoInc varchar(5),
Col2 int,
Col3 int
)
INSERT INTO @Table2
SELECT '00001', 20, 40

DECLARE @Table3 TABLE
(
AutoInc varchar(5),
ColName varchar(5)
)
INSERT INTO @Table3
SELECT '00001', 'Sam' UNION ALL
SELECT '00002', 'Harry' UNION ALL
SELECT '00003', 'Sue' UNION ALL
SELECT '00004', 'Tome'

SELECT t3.ColName, Percentage = ISNULL(m.Percentage, 0)
FROM @Table3 t3
left JOIN
(
SELECT m.Col4, Percentage = CONVERT(decimal(20,2), m.cnt * 100.0 / t.cnt)
FROM
(
SELECT t1.Col4, cnt = COUNT(*)
FROM @Table1 t1 INNER JOIN @Table2 t2
ON t1.Col2 = t2.Col2
AND t1.Col3 = t2.Col3
GROUP BY t1.Col4
) m
INNER JOIN
(
SELECT t1.Col4, cnt = COUNT(*)
FROM @Table1 t1
GROUP BY t1.Col4
) t ON m.Col4 = t.Col4
) m ON t3.ColName = m.Col4
ORDER BY Percentage DESC
/*
ColName Percentage
------- ----------------------
Sue 100.00
Tome .00
Sam .00
Harry .00

(4 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

JustStrolling
Starting Member

15 Posts

Posted - 2007-11-22 : 00:52:20

Wow... I'm impressed.
Simply a great tutorial for everyone.
This will definitely give me and everyone else something to work with...

*Thanks*

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources