Calcualte % on group by query.

[learn07]

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

[Phil Weber]

http://support.microsoft.com/kb/256282

[learn07]

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.