MS SQL - Join with group by

I'm very new to SQL and learing it from the book "SAMS Teach Yourself SQL in 24 hours". My concern is all about joins and grouping the select output. Here is the structure of my tables:

CREATE TABLE ORDERS_TBL
( ORD_NUM VARCHAR(10) NOT NULL primary key,
  CUST_ID VARCHAR(10) NOT NULL,
  PROD_ID VARCHAR(10) NOT NULL,
  QTY INTEGER NOT NULL,
  ORD_DATE DATE );

CREATE TABLE PRODUCTS_TBL
( PROD_ID VARCHAR(10) NOT NULL primary key,
  PROD_DESC VARCHAR(40) NOT NULL,
  COST DECIMAL(6,2) NOT NULL );

What I would like to achive are join operation which would group by my PROD_ID.

SELECT P.PROD_DESC, SUM(O.QTY)
    FROM PRODUCTS_TBL AS P LEFT JOIN ORDERS_TBL AS O
    ON P.PROD_ID = O.PROD_ID
    GROUP BY P.PROD_ID
    ORDER BY P.PROD_ID;

The query above does work for MySQL but not for my MS SQL environment. What does work but is annoying as i don't want to see the PROD_ID in my output.

SELECT P.PROD_ID, P.PROD_DESC, SUM(O.QTY)
    FROM PROCUCTS_TBL AS P LEFT JOIN ORDERS_TBL AS O
    ON P.PROD_ID = O.PROD_ID
    GROUP BY P.PROD_ID, P.PROD_DESC
    ORDER BY P.PROD_ID;

Any hints on how to I get an output table with PROD_DESC, the sum of quantity and group by PROD_ID?

Solution

In TSQL you cannot select a column that is not included in a group by clause (never understood why in mysql you can because it does not make sense IMHO)

A dirty trick is to select with min() or max() or similar function (based on your needs) only one of the not aggregated column row.

SELECT max( P.PROD_DESC), SUM(O.QTY)
FROM PRODUCTS_TBL AS P LEFT JOIN ORDERS_TBL AS O
  ON P.PROD_ID = O.PROD_ID
GROUP BY P.PROD_ID
ORDER BY P.PROD_ID;

Anyway if the P.PROD_DESC column is the same it is better to include it in the Group by clause or if you want all different P.PROD_ID, P.PROD_DESC rows