Calcualte % on group by query.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Calcualte % on group by query.

  1. #1
    Join Date
    Feb 2007
    Posts
    2

    Calcualte % on group by query.

    Hi,

    I am trying to calculated % . My query calculated some results like

    Degree # %
    Doc 12
    Masters 13
    Bach 14
    HS 18
    Right now I have count in 2 column and I need to calculated %. My query is

    SELECT COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# of Members], C.USR_HIGHEST_DEGREE
    FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN
    PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN
    ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN
    CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN
    ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_ID
    WHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND
    (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL'))
    GROUP BY C.USR_HIGHEST_DEGREE


    I am not sure how to calculated % for each group by values. Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Feb 2007
    Posts
    2
    Hi,

    Thanks for your reply but I have done that and it gives me error
    here is the query I was using

    SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))

    / (




    SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))

    FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN
    PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN
    ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN
    CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN
    ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID
    WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND
    (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))





    )*100 AS [%]



    FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN
    PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN
    ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN
    CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN
    ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_ID
    WHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND
    (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL'))
    GROUP BY C.USR_HIGHEST_DEGREE

    It gives me error. I am running this query through a .net application. Due you think that would make a difference.

Similar Threads

  1. SQL2000 remote SQL query
    By lightningtechie in forum Database
    Replies: 1
    Last Post: 02-07-2006, 08:34 AM
  2. reg...select count distinct....query
    By Mohan Ekambaram in forum Database
    Replies: 0
    Last Post: 12-09-2001, 11:56 AM
  3. reg....count distinct query
    By Mohan Ekambaram in forum Database
    Replies: 0
    Last Post: 12-08-2001, 11:21 PM
  4. query using group by
    By Dan Donahue in forum Database
    Replies: 2
    Last Post: 01-11-2001, 04:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center