SQL Query Help

• moorthy.jy

  SSCommitted

  Points: 1753

  More actions

  January 10, 2010 at 3:29 am

  #96790

  Hi,

  I have the following tables and data.

  CREATE TABLE T1 (ItemName VARCHAR(10))

  GO

  CREATE TABLE Trx1 (ItemName VARCHAR(10), Mode VARCHAR(3), Sale1 decimal(12,3))

  GO

  INSERT T1

  SELECT 'A123' UNION ALL

  SELECT 'A1234' UNION ALL

  SELECT 'A12345' UNION ALL

  SELECT 'A123456' UNION ALL

  SELECT 'A1234567' UNION ALL

  SELECT 'A123456789'

  GO

  INSERT Trx1

  SELECT 'A12345','AAA',1 UNION ALL

  SELECT 'A1234567','BBB',1 UNION ALL

  SELECT 'A123456789','AAA',3.5 UNION ALL

  SELECT 'A123','AAA',6 UNION ALL

  SELECT 'A1234567','AAA',12 UNION ALL

  SELECT 'A12345','AAA',4.3 UNION ALL

  SELECT 'A1234567','BBB',3.1

  GO

  And i need the show the results like this. I am stuck here.

  SELECT T1.ItemName, COUNT(T1.ItemName) AS TOTAL -- need to show count(Mode) only 2 modes.

  FROM T1 LEFT OUTER JOIN Trx1 ON T1.ItemName = Trx1.ItemName

  GROUP BY T1.ItemName

  ItemName TOTAL AAA BBB

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

  A123 110

  A1234 100

  A12345 220

  A123456 100

  A1234567 312

  A123456789 110

  Please help me with this query. It would be better, if the code is SQL 2000 compatible.

  Thanks in advance

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  January 10, 2010 at 6:17 am

  #1101496

  I dont understand how 'A123456' would have 1 in total when there are no count in either modes

  Is this what you need?

  SELECT T1.ItemName,

  SUM(CASE WHEN Mode ='AAA' THEN 1 ELSE 0 END) AS AAA,

  SUM(CASE WHEN Mode ='BBB' THEN 1 ELSE 0 END) AS BBB,

  (SUM(CASE WHEN Mode ='AAA' THEN 1 ELSE 0 END) +SUM(CASE WHEN Mode

  ='BBB' THEN 1 ELSE 0 END)) as total

  FROM T1

  LEFT JOIN Trx1

  ON T1.ItemName = Trx1.ItemName

  GROUP BY T1.ItemName

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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 10, 2010 at 6:23 am

  #1101497

  Something along those lines?

  Note: The CASE statement is used to set a "flag" depending on the mode and then simply adding the "Flags"...

  SELECT T1.ItemName, COUNT(T1.ItemName) AS TOTAL,

  sum(case WHEN Mode='AAA' THEN 1 ELSE 0 END) AS 'AAA',

  sum(case WHEN Mode='BBB' THEN 1 ELSE 0 END) AS 'BBB'

  FROM T1 LEFT OUTER JOIN Trx1 ON T1.ItemName = Trx1.ItemName

  GROUP BY T1.ItemName

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• moorthy.jy

  SSCommitted

  Points: 1753

  More actions

  January 10, 2010 at 8:59 am

  #1101512

  Thanks Nabha and Lutz.

  Nabha, The solution looks for all rows in T1 (even, if there no trx in Trx1 table)

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  January 11, 2010 at 12:23 am

  #1101634

  Yes I got it, Thanks for updating back 🙂

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