Sql-server – group by and having query need help

sql-server-2005

I am using the pub database in SQL Server 2005. Which stores have at least 5 orders? Should diplay the store name and store id. I have come up with a query something like this:

select 
    stores.stor_name
    , sales.stor_id  
from 
    sales 
join 
    stores 
on  sales.stor_id =stores.stor_id 
group by 
    sales.stor_id  
having 
    (count(sales.stor_id)>=5)

When answering please mention what i have done wrong

Best Answer

ypercube already gave the right answer, but here's another alternative:

Instead of GROUPing by all columns (leading to a bloated GROUP BY this, that, that, and_that_too), the following can be used:

select 
    sales.stor_id,  
    MIN(stores.stor_name) AS stor_name
from 
    sales 
join 
    stores 
on  sales.stor_id =stores.stor_id 
group by 
    sales.stor_id  
having 
    (count(sales.stor_id)>=5)

Though I confess we just move the bloating around... But this at least clarifies the mechanism of the query, by simplifying the GROUP BY.

Related Solutions

SQL Server – Query Running Differently on SQL 2005 vs SQL 2008 R2

I'd like you to rework the query.

You have sargability issues, and are even using scalar function calls in there, which will be hurting the query too. You may want to make a FullName computed column on Table2 and put an index on that, making sure your index INCLUDEs FirstName and LastName. You should also add indexes that help the other

Also, make an inline table-valued function to do your "RemoveNonLetter" functionality, and rework your query to use that, probably using APPLY as I've done here.

And definitely check that bug that Paul's answer refers to.

INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT 
 Table1.iGroupID,
 GETDATE()
FROM Table1
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FullName)) AS fn (FullName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FamilyName)) AS famn (FamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.Child1Name)) AS c1n (Child1Name)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyName)) AS sfn (StepFamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyNameChild2)) AS sfnc2 (StepFamilyNameChild2)
WHERE 
 NOT EXISTS (
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FullName = fn.FullName
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FamilyName = famn.FamilyName AND Alias2.Child1Name = c1n.Child1Name
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.StepFamilyName = sfn.StepFamilyName AND Alias2.StepFamilyNameChild1 = sfnc2.StepFamilyNameChild2
 ) 
 AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )
;

Sql-server – Change SQL Server 2005 from Standard Edition to Workgroup

Standard to Workgroup is a downgrade

This is the BOL page on upgrades

Unfortunately, you'll have to uninstall and reinstall. On the bright side, the master, msdb etc will simply restore into the "lower" version as long the version number is the same.

Best Answer

Related Solutions

SQL Server – Query Running Differently on SQL 2005 vs SQL 2008 R2

Sql-server – Change SQL Server 2005 from Standard Edition to Workgroup

Related Question