how to write a query to get the count of activties depending on condition from a single table RSS

• • Reply
  

  anilr499

  Member

  94 Points

  389 Posts

  how to write a query to get the count of activties depending on condition from a single table

  Dec 07, 2012 01:50 AM|LINK

  how to write a query to get the count of total activities,count(act) where status='c'
  ,coun(act) where status='o' in single table from single table
  
  ex:

  i have a table name 'A' in which i have all the activities with status='c'
  and status='o'
  now i need aquery to get tha table 'B' like this

  totcount    cnt(act where status='c')   cnt(act where status='o')    

   250              100                                    150

• • Reply
  

  kedarrkulkar...

  All-Star

  34545 Points

  5554 Posts

  Re: how to write a query to get the count of activties depending on condition from a single table

  Dec 07, 2012 02:21 AM|LINK

  try this

  Select
  A.Count_C + A.Count_O as 'Total', A.Count_C, A.Count_O
  from
  (
  Select
    (select count(act) from tableName where status='c') as Count_C,
    (select count(act) from tableName where status='o') as Count_O
  ) ASelect

  hope this helps...

  Cheers!
  KK
  
  Please mark as Answer if post helps in resolving your issue
  My Site
• • Reply
  

  imobsuz

  Participant

  1278 Points

  195 Posts

  Re: how to write a query to get the count of activties depending on condition from a single table

  Dec 07, 2012 03:27 AM|LINK

  Try:

  select
      count(*) as TotCount,
      sum(case when act = 'c' then 1 else 0 end) as CountC,
      sum(case when act = 'o' then 1 else 0 end) as CountO
  from MyTable

  Hope this helps.

  http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos
  • Marked as answer by Chen Yu - MSFT on Dec 10, 2012 06:20 AM