# Thread: Need help with 2 view calculation

1. Registered User
Join Date
Oct 2009
Posts
93

## Unanswered: Need help with 2 view calculation

Have been at this all day and can't seem to find a solution

I have 2 views

View 1
Total | Type
200 | 1
300 | 2
240 | 1

View 2
Total | Type
600 | 1
700 | 2

I need to sum the totals in View 1 that have the same type and then divide it by the total in View 2 with same type so i can get a percentage

e.g. 200 + 240 (from View1) / 600 (from View 2) * 100 = 73.3%

I have tried the following and many variations but getting nowwhere

SELECT SUM(dbo.View1.Total) / Sum(dbo.View2.Total * 1e0)
from dbo.View1 INNER JOIN dbo.View2 ON
dbo.View1.type = dbo.View2.type

2. Registered User
Join Date
Nov 2004
Posts
1,428
You were close. Your script made the sum of all the Totals in all records in View1 and View2. What you need is to get the sum of all Totals PER Type. All what was missing is a GROUP BY Type (and a multiply by 100.0 to get a percentage)
Code:
```SELECT	dbo.View1.type,
CAST(SUM(dbo.View1.Total) * 100.0 / Sum(dbo.View2.Total) AS DECIMAL(5, 2)) as Pct
from dbo.View1
INNER JOIN dbo.View2 ON
dbo.View1.type = dbo.View2.type
GROUP BY dbo.View1.type```

3. Registered User
Join Date
Oct 2009
Posts
93
Thanks Wim

4. Registered User
Join Date
Nov 2004
Posts
1,428
I recently got an offer for a beer when I would pass in Dublin. Now I got another one, at least I will interpret "" as a free beer offer.

Is this the beginning of a new wave or is it mere wishful thinking? Anyway, I will start to keep a score of "Earned beers".

It's also a bit confrontational for me, to realise I have only earned 2 free beers so far, during the two years I am active on this SQL Server forum.
Last edited by Wim; 06-14-11 at 12:53.

5. Registered User
Join Date
Oct 2009
Posts
93
You would definately get a free beer off me Wim if in Ireland

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•