help with complex query

Here is one way:

select Legend='Status 1', count(Weigh),sum(Weigh) from myTable where id=120 and status=0
UNION
select Legend='Status 2',count(Weigh),sum(Weigh) from myTable where id=120 and status=2
UNION
select Legend='Status 3',count(Weigh),sum(Weigh) from myTable where id=120 and status=3
ORDER BY Legend

---

Kalman Toth Database & OLAP Architect sqlusa.com

New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012