Posted on 2005-05-16
Last Modified: 2010-03-19
I need to merge the following.... Below is the query that simply brings through the total number of contacts  per category

SELECT     COUNT(*) AS [Grand Total], Contact
FROM         DMR_Referrals
GROUP BY Contact, New_Fup, Contact
HAVING      (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')

What I also need too do is with regards too the below

SELECT     COUNT(*) AS [Grand Total], Contact, refReason1
FROM         DMR_Referrals
GROUP BY Contact, New_Fup, Contact, refReason1
HAVING      (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')

Show as a percentage each refReason1 of the grand total...

So if we say contact D has a grand total of 100, then I need refReason1 to be represented as the percentage of contact D.

Can anyone show me how to do this?
Question by:paulo111
    1 Comment
    LVL 13

    Accepted Solution

    SELECT refReason1, Contact,100.*A.[Grand Total]/B.[Grand Total] [Grand Total Pct]
    SELECT     refReason1, Contact, COUNT(*) AS [Grand Total]  
    FROM         DMR_Referrals
    WHERE     (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')
    GROUP BY refReason1,Contact
    ) A
    SELECT     Contact, COUNT(*) AS [Grand Total]  
    FROM         DMR_Referrals
    WHERE     (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')
    GROUP BY Contact
    ) B ON A.Contact=B.Contact

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now