count by querey

• pallavi.unde

  SSCommitted

  Points: 1987

  More actions

  December 10, 2011 at 3:27 am

  #251777

  I am executing query,

  select b.StreamGroupId,b.StreamGroupName,b.DegreeId,

  distinct count(a.collegeid) as becount

  from college_degreestreamtransition a, college_streamgroupmaster b

  where b.PopularStream='Y'

  and b.degreeid=41

  and a.streamgroupid like '%,89,%'

  group by b.StreamGroupId,b.StreamGroupName,b.DegreeId

  but it is showing error :- Msg 156, Level 15, State 1, Line 1

  Incorrect syntax near the keyword 'distinct'.

  and i need to use distinct

  also need to sequence the column i have taken in query same as given above.

  Thanks & Regards,
  Pallavi

• Devendrakumar

  SSC-Forever

  Points: 42493

  More actions

  December 10, 2011 at 3:33 am

  #1419699

  SELECT [ ALL | DISTINCT ]

  [ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ]

  <select_list>

  <select_list> ::=

  {

  *

  | { table_name | view_name | table_alias }.*

  | {

  [ { table_name | view_name | table_alias }. ]

  { column_name | $IDENTITY | $ROWGUID }

  | udt_column_name [ { . | :: } { { property_name | field_name }

  | method_name (argument [ ,...n] ) } ]

  | expression

  [ [ AS ] column_alias ]

  }

  | column_alias = expression

  } [ ,...n ]

  distinct keyword should appear before all columns.

  OR

  COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

  inside count function.