how to group the items by item id

I have a table named TRNSPOINDT. TRNSPOINDT has three fields named as itemid, 开发者_如何学运维Qty, projectname. I need to group the items by itemid and display the sum of Qty with respect to the itemid. I have done this, the query is as follows,

 SELECT ITMID ,SUM(QTY) AS QTY FROM TRNSPOINDT GROUP BY ITMID 

The problem is, i should display the project name respective to the itemid. But while iam trying to display project name, i got a error.

I have tried this query,

    SELECT ITMID ,SUM(QTY) AS QTY,PROJECTNAME FROM TRNSPOINDT GROUP BY ITMID 

I got a error as,

   Column 'TRNSPOINDT.PROJECTNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know that, i got this error because there is more than one project name is exist for a single itemid. But i should display the project name near to the itemid. How to do it..

Thanks in advance, Praveen.T

---

The reason for this error is that it doesn't make sense to include the project name in this query. For example suppose you had

itmid   qty   projectname
    1     3         proj1
    1     7         proj2
    1     2         proj1
    3     4         proj1

You want rows for

itmid   qty
    1    12
    3     4

But how would you attach a projectname to the first result row?

You mention in your question that you know the reason, but it really doesn't have a solution, other than grouping by item and project together, or creating a string of comma-separated project names for the items (which isn't exactly "denormalized", but is maybe what you want).

Do you mean this:

SELECT ITMID, SUM(QTY) AS QTY, PROJECTNAME 
FROM TRNSPOINDT 
GROUP BY ITMID, PROJECTNAME

It would give

itmid   qty   projectname
    1     5         proj1
    1     7         proj2
    3     4         proj1

It groups by all itemid-projectname combinations.

---

Two ways depending on what 'exactly' you are looking for. First add projectname column in group by.

SELECT ITMID ,SUM(QTY) AS QTY,PROJECTNAME FROM TRNSPOINDT GROUP BY ITMID, PROJECTNAME

ITMID     Sum(QTY)      PROJECTNAME
-----------------------------------
itm1      10            PROJ1
itm1      20            PROJ2
itm2      12            PROJ3

This will display projectname in result, but the sum will be calculated for Qty for ProjectNAME and not by ITMID as you say there are more than one PROJECTNAME for one ITMID.

If you dont want Sum to be calculated for PROJECTNAME but only by ITMID. Then you will have to use subquery.

SELECT B.ITMID , B.QTY, A.PROJECTNAME
FROM TRANSPOINTDT As A INNER JOIN (SELECT ITMID ,SUM(QTY) AS QTY,PROJECTNAME FROM TRNSPOINDT GROUP BY ITMID) as B
ON A.ITMID = B.ITMID

ITMID      SUM(QTY)      PROJECTNAME
------------------------------------
itm1       30            PROJ1
itm2       12            PROJ3

Though this does not make sense to add PROJECTNAME column as it will not represent the correct information.