Homework Expanded I

I labeled this post "Expanded I" because I'm sure that I"m going to be doing this again in the future... essentially what I did was expanded on some home work that I've already done.

Tonight I started with this as my homework and was going to work through it real quick, it's from chapter four:

1) Use production.products table to group products by categoryid, and returns for each category the minimum unit price. 

2) define a CTE based on question 1 and join the CTE to the production.products table to return per each category the products with the minimum unit price.

Then I decided to take it a little further and said... what if I wanted an additional column that told me how many products where in categoryid, then out of those categories, which one had the smallest unit price. It took me a while, but I finally got it... the batch of code is below.  I'm going to post to SSC and see if there is different way of doing this that may be easier. I don't there is because I have two aggregates.  I just created two CTE's in order to implement this.

WITH cte AS(
SELECT COUNT(categoryid) AS catct, categoryid
FROM production.Products
GROUP BY categoryid),
ctea AS(
SELECT MIN(unitprice) AS mn, categoryid
FROM production.Products
GROUP BY categoryid)
SELECT a.categoryid, a.catct, b.mn
FROM cte AS a
INNER JOIN ctea AS b
ON b.categoryid = a.categoryid

More XML tomorrow, I watched some youtube videos on XML today to try to get comfortable with whole elements, attributes, and namespaces.  I'm going to try to articulate what I learn tomorrow with a post in the afternoon.  Cheers until then.