Calculating Percentages

• timotech

  SSCrazy

  Points: 2698

  More actions

  August 2, 2010 at 2:14 am

  #223953

  Hi All, please i have this sql:

  SELECT SUM(t .Amount) AS TotalAmount, b.Description AS Brand, 'TV Total' AS Medium

  FROM dbo.tbl_Television AS t INNER JOIN

  dbo.tbl_Brand AS b ON t .FK_ProductId = b.FK_ProductId AND t .FK_BrandId = b.BrandId

  WHERE (t .FK_ProductId = @ProductId) AND (t .AdDate BETWEEN @BeginDate AND @EndDate)

  GROUP BY b.Description

  ORDER BY Brand

  That gives me this result:

  Insert into #mytable(TotalAmount, Brand, Medium)

  SELECT '62266962', 'ETISALAT(EMTS)', 'TV Total' UNION ALL

  SELECT '33127444', 'GLOBACOM', 'TV Total' UNION ALL

  SELECT '149755493', 'MTN', ' TV Total' UNION ALL

  SELECT '78444', 'VISAFONE', 'TV Total' UNION ALL

  SELECT '18456497', 'ZAIN', 'TV Total' UNION ALL

  SELECT '194632', 'ZOOM MOBILE', 'TV Total' UNION ALL

  what if i want a result that will display the percentages, such the percentage of each individual TotalAmount from the Sum(TotalAmount) e.g like this:

  Insert into #mytable(TotalAmount, Brand, Medium, Percentages)

  SELECT '62266962', 'ETISALAT(EMTS)', 'TV Total', 7% UNION ALL

  SELECT '33127444', 'GLOBACOM', 'TV Total', 18% UNION ALL

  SELECT '149755493', 'MTN', ' TV Total', '21%' UNION ALL

  SELECT '78444', 'VISAFONE', 'TV Total', '12%' UNION ALL

  SELECT '18456497', 'ZAIN', 'TV Total', '8%' UNION ALL

  SELECT '194632', 'ZOOM MOBILE', 'TV Total', '13%' UNION ALL

  something of this nature may be from a formula (TotalAmount/Sum(TotalAmount)) * 100

  Thanks for your help, its a bit difficult for me to write.

  Timotech

• August 2, 2010 at 3:04 am

  This was removed by the editor as SPAM

• timotech

  SSCrazy

  Points: 2698

  More actions

  August 2, 2010 at 3:21 am

  #1200886

  Hi Stewartc, thanks so much it worked. I appreciate.

  I want to try some more experiment, i let u know if that works, then i want to ask, how do i do it in ssrc, that u suggested.

  Thanks

  Timotech

• August 2, 2010 at 3:34 am

  This was removed by the editor as SPAM

• timotech

  SSCrazy

  Points: 2698

  More actions

  August 2, 2010 at 5:54 am

  #1200937

  Hi Stewartc, thanks for your reply, my internet connection is misbehaving, but i'll get back to you later.

  Thanks

  Timotech