Results 1 to 5 of 5
  1. #1
    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. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2009
    Posts
    93
    Thanks Wim

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    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
  •