Need help with SQL statement

• tawfey

  Newbie

  Points: 1

  More actions

  July 22, 2009 at 7:25 pm

  #132117

  Hi all,

  First time user of SQL here, not very familiar with SQL statement, need help with this situation here.

  I have a table which is something like this

  BranchID ClientID ClientActivity

  -------------------------------------

  1 135 Open Account

  1 135 deposit

  1 135 enquiry

  1 123 enquiry

  1 234 enquiry

  2 246 withdrawal

  2 246 enquiry

  2 345 enquiry

  2 345 withdrawal

  3 357 deposit

  3 357 enquiry

  I need to achieve the following result

  BranchID NumberOfClient

  --------------------------

  1 4

  2 2

  3 1

  How could I formulate the SQL statement?

  thanks,

  Tawfey

• Lowell

  SSC Guru

  Points: 323755

  More actions

  July 22, 2009 at 7:42 pm

  #1028733

  CREATE TABLE #WHATEVER(

  BranchID int,

  ClientID int,

  ClientActivity varchar(30) )

  -------------------------------------

  INSERT INTO #WHATEVER

  SELECT 1, 135,'Open Account' UNION ALL

  SELECT 1, 135,'deposit' UNION ALL

  SELECT 1, 135,'enquiry' UNION ALL

  SELECT 1, 123,'enquiry' UNION ALL

  SELECT 1, 234,'enquiry' UNION ALL

  SELECT 2, 246,'withdrawal' UNION ALL

  SELECT 2, 246,'enquiry' UNION ALL

  SELECT 2, 345,'enquiry' UNION ALL

  SELECT 2, 345,'withdrawal' UNION ALL

  SELECT 3, 357,'deposit' UNION ALL

  SELECT 3, 357,'enquiry'

  SELECT BranchID, COUNT(DISTINCT ClientID) AS CLIENTCOUNT

  FROM #WHATEVER

  GROUP BY BranchID

  --results:

  BranchID CLIENTCOUNT

  ----------- -----------

  1 3

  2 2

  3 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!