Count (*)

GRChandrashekar
GRChandrashekar used Ask the Experts™
on
I have the following query

SELECT M.MEMBER_ID, MF.USERNO
  FROM MEMBER M
       INNER JOIN MEMBERFAMILY MF
          ON M.MEMBER_ID = MF.MEMBER_ID
       INNER JOIN CATEGORYSUBGROUP CS
          ON CS.CATEGORYSUBGROUP_ID = M.CATEGORYSUBGROUP_ID
       INNER JOIN CATEGORY CT
          ON CT.CATEGORY_ID = MF.USERCATEGORY_ID
 WHERE     CT.ISDEPENDENT = 1
       AND CS.ISDEPENDENT = 1
       AND MF.FLAG = -1
       AND (CS.ISTEMPORARYMEMBER = 2 OR CS.ISINTERNALCONSUMPTION = 2)
       AND M.ISCONTROVERSIAL = 2
       AND MF.MODIFIEDDATE BETWEEN TRUNC (TRUNC (SYSDATE, 'MM') - 1, 'MM')
                                   + 14
                               AND  TRUNC (SYSDATE, 'MM') - 1
UNION ALL
SELECT M.MEMBER_ID, MF.USERNO
  FROM MEMBER M
       INNER JOIN MEMBERFAMILY MF
          ON M.MEMBER_ID = MF.MEMBER_ID
       INNER JOIN CATEGORYSUBGROUP CS
          ON CS.CATEGORYSUBGROUP_ID = M.CATEGORYSUBGROUP_ID
       INNER JOIN CATEGORY CT
          ON CT.CATEGORY_ID = MF.USERCATEGORY_ID
 WHERE     CT.ISDEPENDENT = 1
       AND CS.ISDEPENDENT = 1
       AND MF.FLAG = 0
       AND (CS.ISTEMPORARYMEMBER = 2 OR CS.ISINTERNALCONSUMPTION = 2)
       AND M.ISCONTROVERSIAL = 2

Open in new window


Sample Output is

MEMBER_ID       USERNO
2673                   S-381B
2673                   S-381C

What I need actually is

MEMBER_ID       COUNT
2673                      2
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think you may want to use GROUP BY Member_ID
Top Expert 2011

Commented:
- take out USERNO from your select, replace with COUNT(*) and add GROUP BY MEMBER_ID
Top Expert 2011

Commented:
- can i ask, the answer that you accepted - with only GROUP BY and not remove the USERNO, how do you get the COUNT?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial