Sql Query

• sajjadkaswani 43055

  SSC Rookie

  Points: 36

  More actions

  May 13, 2017 at 5:14 am

  #330234

  SQL Server 2008

  My Table:

  InstructionID
  CompanyCode
  ProductType (F/I/P/C/…)
  Amount

  Output :

  I wish to have a query to return output like

  CompanyCode, Count of F, Sum of F, Count of I, Sum of I, Count of P, Sum of P , Count of C, Sum of C

  Kindly assist me

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  May 13, 2017 at 5:35 am

  #1942423

  sajjadkaswani 43055 - Saturday, May 13, 2017 5:14 AM

  SQL Server 2008

  My Table:

  InstructionID
  CompanyCode
  ProductType (F/I/P/C/…)
  Amount

  Output :

  I wish to have a query to return output like

  CompanyCode, Count of F, Sum of F, Count of I, Sum of I, Count of P, Sum of P , Count of C, Sum of C

  Kindly assist me

  Thanks

  Group by CompamyCode and ProductType
  😎

• sajjadkaswani 43055

  SSC Rookie

  Points: 36

  More actions

  May 13, 2017 at 5:44 am

  #1942424

  Eirikur Eiriksson - Saturday, May 13, 2017 5:35 AM

  sajjadkaswani 43055 - Saturday, May 13, 2017 5:14 AM

  SQL Server 2008

  My Table:

  InstructionID
  CompanyCode
  ProductType (F/I/P/C/…)
  Amount

  Output :

  I wish to have a query to return output like

  CompanyCode, Count of F, Sum of F, Count of I, Sum of I, Count of P, Sum of P , Count of C, Sum of C

  Kindly assist me

  Thanks

  Group by CompamyCode and ProductType
  😎

  I wish to have output like this.

  Company Name | Count of F | Sum of F | Count of I | Sum of I | Count of P | Sum of P

  Thanks

• sajjadkaswani 43055

  SSC Rookie

  Points: 36

  More actions

  May 13, 2017 at 5:49 am

  #1942425

  sajjadkaswani 43055 - Saturday, May 13, 2017 5:44 AM

  Eirikur Eiriksson - Saturday, May 13, 2017 5:35 AM

  sajjadkaswani 43055 - Saturday, May 13, 2017 5:14 AM

  SQL Server 2008

  My Table:

  InstructionID
  CompanyCode
  ProductType (F/I/P/C/…)
  Amount

  Output :

  I wish to have a query to return output like

  CompanyCode, Count of F, Sum of F, Count of I, Sum of I, Count of P, Sum of P , Count of C, Sum of C

  Kindly assist me

  Thanks

  Group by CompamyCode and ProductType
  😎

  I wish to have output like this.

  Company Name | Count of F | Sum of F | Count of I | Sum of I | Count of P | Sum of P

  Thanks

  I got it

  SELECT CompanyCode   , [CountOfF] = SUM(CASE WHEN ProductType = 'F' THEN 1 ELSE 0 END)   , [SumOfF] = SUM(CASE WHEN ProductType = 'F' THEN Amount ELSE 0 END)   ...FROM MyTableWHERE ...GROUP BY CompanyCodeORDER BY CompanyCode

  Thanks

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  May 15, 2017 at 1:24 am

  #1942476

  sajjadkaswani 43055 - Saturday, May 13, 2017 5:49 AM

  sajjadkaswani 43055 - Saturday, May 13, 2017 5:44 AM

  Eirikur Eiriksson - Saturday, May 13, 2017 5:35 AM

  sajjadkaswani 43055 - Saturday, May 13, 2017 5:14 AM

  SQL Server 2008

  My Table:

  InstructionID
  CompanyCode
  ProductType (F/I/P/C/…)
  Amount

  Output :

  I wish to have a query to return output like

  CompanyCode, Count of F, Sum of F, Count of I, Sum of I, Count of P, Sum of P , Count of C, Sum of C

  Kindly assist me

  Thanks

  Group by CompamyCode and ProductType
  😎

  I wish to have output like this.

  Company Name | Count of F | Sum of F | Count of I | Sum of I | Count of P | Sum of P

  Thanks

  I got it

  SELECT CompanyCode   , [CountOfF] = SUM(CASE WHEN ProductType = 'F' THEN 1 ELSE 0 END)   , [SumOfF] = SUM(CASE WHEN ProductType = 'F' THEN Amount ELSE 0 END)   ...FROM MyTableWHERE ...GROUP BY CompanyCodeORDER BY CompanyCode

  Thanks

  Great. Glad you found the solution yourself.
  Here's an article on the method that you have used which might be useful.
  http://www.sqlservercentral.com/articles/T-SQL/63681/

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/