Group by clause - question

• rash3554

  SSCommitted

  Points: 1984

  More actions

  April 2, 2015 at 4:50 am

  #302718

  I am trying to print Companies with less than 100 employees for all dates.

  Here's my table structure

  Create table CompanyEmployeeArchive(

  Company varchar(100) not null, Employees int, Dateinserted date)

  Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01')

  Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01')

  Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01')

  Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01')

  Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01')

  Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01')

  Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01')

  Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')

  I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.

  Select Company,dateinserted,employees from CompanyEmployeeArchive

  group by company,dateinserted,employees having employees < 100 order by company,dateinserted

  this query lists Apple too.

  How can I change the query so Apple does not show up in the list.

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 2, 2015 at 5:23 am

  #1788246

  This is one way, there are others

  SELECT * FROM CompanyEmployeeArchive cea WHERE NOT EXISTS (SELECT 1 FROM CompanyEmployeeArchive c WHERE c.Employees >= 100 AND cea.Company = c.Company)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• MadAdmin

  SSChampion

  Points: 11261

  More actions

  April 2, 2015 at 5:24 am

  #1788247

  SELECTCEA.Company, CEA.dateinserted, CEA.employees

  FROM( SELECTCompany, dateinserted, employees,

  RANK() OVER ( PARTITION BY company ORDER BY employees DESC ) AS Ranking

  FROMCompanyEmployeeArchive ) window

  INNER JOIN CompanyEmployeeArchive AS CEA ON window.company = CEA.company

  WHEREwindow.employees < 100 AND

  window.Ranking = 1

  Catch-all queries done right [/url]
  Gail Shaw's Performance Blog[/url]

• Neil Burton

  SSC-Insane

  Points: 22999

  More actions

  April 2, 2015 at 5:27 am

  #1788249

  with cte_emp as

  (

  select

  m_employees = max(employees)

  ,company

  from CompanyEmployeeArchive

  group by company

  )

  select

  e.Company

  ,dateinserted

  ,employees

  from CompanyEmployeeArchive e

  join cte_emp ce on ce.company = e.company

  where ce.m_employees < 100

  group by e.Company,dateinserted,employees

  This is another option.

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  April 2, 2015 at 7:15 am

  #1788295

  SELECT Company

  FROM CompanyEmployeeArchive

  GROUP BY Company

  HAVING COUNT(*) = SUM(SIGN(100-Employees))

  Far away is close at hand in the images of elsewhere.
  Anon.