Help me about GROUP BY

Question

Post reply

Help me about GROUP BY

Pham Trung

SSC Enthusiast

Points: 183
More actions
November 18, 2005 at 7:17 pm

#95738

I have a table with structure:
Inv_Date, Inv_No, Description, Amount
Error when i execute my command:
CREATE TABLE #Trans (Inv_Date SmallDatetime, Inv_No Char(8), Description Varchar(64), Amount Numeric(9))
INSERT INTO #Trans (Inv_Date, Inv_No, Description, Amount) VALUES (GETDATE(), '1', 'The 1', 10)
INSERT INTO #Trans (Inv_Date, Inv_No, Description, Amount) VALUES (GETDATE(), '2', 'The 2', 20)
INSERT INTO #Trans (Inv_Date, Inv_No, Description, Amount) VALUES (GETDATE(), '3', 'The 3', 30)
INSERT INTO #Trans (Inv_Date, Inv_No, Description, Amount) VALUES (GETDATE(), '1', 'The 1', 1)
SELECT Inv_Date, Inv_No, Description, SUM(Amount) FROM #Trans GROUP BY Inv_No
DROP TABLE #Trans
This is message from SQLSERVER
Server: Msg 8120, Level 16, State 1, Line 7
Column '#Trans.Inv_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 7
Column '#Trans.Description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
----
Help me, becouse i don't want add Inv_date and Description in select list
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

venkat-216866 SSC Veteran Points: 257 More actions · Answer 1

As per your example this will do

SELECT Inv_Date, Inv_No, Description, SUM(Amount)

FROM #Trans

GROUP BY Inv_Date, Inv_No, Description

Inv_Date Inv_No Description Sum

2005-11-19 13:10:00 1 The 1 11

2005-11-19 13:10:00 2 The 2 20

2005-11-19 13:10:00 3 The 3 30

------------------------------------

If you expect some other results.. Please explain your forum with example.. So that we can help you accordingly..

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Read BOL on "group by"...here's an introduction which forms the basis of all groupings...

"When GROUP BY is specified, either each column in any non-aggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must match exactly the select list expression."

**ASCII stupid question, get a stupid ANSI !!!**

Pham Trung SSC Enthusiast Points: 183 More actions · Answer 3

Thank you for your replies. I am new in MS SQL, I working with Foxpro. I upgrating my software to MS SQL. That is ok in foxpro. Now I find other way to do it

Thanks