Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

SQL Server crosstab - can i add a percentage column for each field?

I am producing crosstabs in SQL and need to add a percentage column next to each of the field value columns. for example, in the code below, starting with column DM, the next column should be [PERCENT OF DM], where this should be the percent of the total of the DM field - this would add up to 100 for each column at the bottom of the xtab.

Is this possible? I've searched around on the web to no avail.
select MRC_YEAR,MRCRNG,[DM] ,[DM DMURL] ,[SPEC FIN],[NA] 
FROM 
(SELECT MRC_YEAR,MRCRNG,icontactid,CHANNEL,mrccode
FROM house where drops is null)    ps
PIVOT
(
Count (icontactid)
FOR CHANNEL IN
([DM] ,[DM DMURL] ,[SPEC FIN] ,[NA] )
)  AS pvt 
order by mrc_year,mrccode

Open in new window

0
ND_2007
Asked:
ND_2007
  • 2
  • 2
  • 2
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
Is their a unique field in the cross tab results?  I had a similar situation about a month ago and I ended up just writing a second cross tab query w/ the percentages and then joining the results of both cross tabs together in a query.  Not sure if this will be posible in your scenario though.
0
 
ND_2007Author Commented:
Well, I could make a unique field based on two fields, but I expect to this for around 50 crosstabs, so hopefully i can get each crosstab in one query.  I might try this if all else fails though
0
 
SharathData EngineerCommented:
you have to try like this
SELECT MRC_YEAR,MRCRNG,
       [DM] ,[DM]*100.0/(SELECT COUNT(*) FROM house WHERE drops IS NULL AND CHANNEL = 'DM') as Percentage_DM,
       [DM DMURL] ,[DM DMURL]*100.0/(SELECT COUNT(*) FROM house WHERE drops IS NULL AND CHANNEL = 'DM DMURL') as [Percentage_DM DMURL],
       [SPEC FIN],[SPEC FIN]*100.0/(SELECT COUNT(*) FROM house WHERE drops IS NULL AND CHANNEL = 'SPEC FIN') as [Percentage_SPEC FIN],
       [NA] 
 FROM (SELECT MRC_YEAR,MRCRNG,icontactid,CHANNEL,mrccode
         FROM house where drops is null)    ps
        PIVOT (Count (icontactid) FOR CHANNEL IN ([DM] ,[DM DMURL] ,[SPEC FIN] ,[NA] ))  AS pvt 
order by mrc_year,mrccode

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ND_2007Author Commented:
That's the ticket......thanks!!
0
 
Christopher GordonSenior Developer AnalystCommented:
Nice Job Sharath_123, I'll be trying that one out in the future too :)
0
 
SharathData EngineerCommented:
thanks, happy to help you.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now