2 popular queries I can never remember

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

2 popular queries I can never remember

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-08-20 : 09:07:19

Hi,
2 tables:
Employee (employeeID, name, saleCount)
Sales (employeeID, sold, CreatedDate)

Query#1 List employees and the sum of their sales, ordered by highest to lowest.

Query#2 Update the Employee.SaleCount field but suming the Sales.Sold column for employeeID = 101

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 09:13:26

After over 300 posts, you can't remember GROUP BY, SUM and ORDER BY?

E 12°55'05.25"
N 56°04'39.16"

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-08-20 : 10:05:32

Peso,

Is this correct?

Query#1

SELECT e.*, SUM(s.sold)
FROM employee e
inner join sales s on (e.employeeid = s.employeeid)
order by sum(s.sold)

?

Query#2 I have to do an inner join in the Update statement....?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 10:10:10

quote:
Originally posted by sql777

Peso,

Is this correct?

Query#1

SELECT e.name, SUM(s.sold)
FROM employee e
inner join sales s on (e.employeeid = s.employeeid)
group by e.name
order by sum(s.sold)

?

Query#2 I have to do an inner join in the Update statement....?

make like above

2.

Update e
  SET e.saleCount=s.SumSold
  FROM Employee e
  CROSS APPLY (SELECT Sum(Sold) AS SumSold 
  FROM Sales 
  ON employeeid = s.employeeid)s
  WHERE e.employeeID = 101

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 10:10:15

Try in your query engine to see if query is correct.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 03:52:54

quote:
Originally posted by sql777

Peso,

Is this correct?

Query#1

SELECT e.*, SUM(s.sold)
FROM employee e
inner join sales s on (e.employeeid = s.employeeid)
order by sum(s.sold)

?

Query#2 I have to do an inner join in the Update statement....?

Not correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources