Group by clause in XL

Hi

I'm afraid we can't help without seeing the SQL you are trying to run - can you post it? Thanks.

Richard

SELECT `'Delta Report Data$'`.Segment, `'Delta Report Data$'`.SBU, `'Delta Report Data$'`.Division, `'Delta Report Data$'`.Operation, `'Delta Report Data$'`.`Business Unit`, `'Delta Report Data$'`.Team, `'Delta Report Data$'`.`Product Category`, `'Delta Report Data$'`.Product, `'Delta Report Data$'`.`Additional Info`, `'Delta Report Data$'`.`User ID`, `'Delta Report Data$'`.`User Contact Name and BP ID`, `'Delta Report Data$'`.Email, `'Delta Report Data$'`.`Staff Number`, `'Delta Report Data$'`.`Work Order`, `'Delta Report Data$'`.`Asset Tag`, `'Delta Report Data$'`.`Job Recovery Code`, `'Delta Report Data$'`.`Service Start Date`, `'Delta Report Data$'`.`Service End Date`, `'Delta Report Data$'`.`Start Month`, `'Delta Report Data$'`.`End Month`, `'Delta Report Data$'`.`RFS Opener`, `'Delta Report Data$'`.`RFS Closer`, `'Delta Report Data$'`.`Previous Year Ref Figure (March)`, `'Delta Report Data$'`.April, `'Delta Report Data$'`.May, `'Delta Report Data$'`.June, `'Delta Report Data$'`.July, `'Delta Report Data$'`.August, `'Delta Report Data$'`.September, `'Delta Report Data$'`.October, `'Delta Report Data$'`.November, `'Delta Report Data$'`.December, `'Delta Report Data$'`.January, `'Delta Report Data$'`.February, `'Delta Report Data$'`.March, `'Delta Report Data$'`.`April Delta`, `'Delta Report Data$'`.`May Delta`, `'Delta Report Data$'`.`June Delta`, `'Delta Report Data$'`.`July Delta`, `'Delta Report Data$'`.`August Delta`, `'Delta Report Data$'`.`September Delta`, `'Delta Report Data$'`.`October Delta`, `'Delta Report Data$'`.`November Delta`, `'Delta Report Data$'`.`December Delta`, `'Delta Report Data$'`.`January Delta`, `'Delta Report Data$'`.`February Delta`, `'Delta Report Data$'`.`March Delta`, Sum(`'Delta Report Data$'`.`Year to Date`)
FROM `D:\GRoup IS Billing\Group IS Billing`.`'Delta Report Data$'` `'Delta Report Data$'`
Group By `'Delta Report Data$'`.`Business Unit`


The coloured area is the addition i made into the SQL generated by using Microsoft query

Vaishak

Paste the following over the SQL (in MS Query) and run the query (usually just by clicking the OK button in the SQL window):

SELECT T.Segment, T.SBU, T.Division, T.Operation, T.[Business Unit],
 T.Team, T.[Product Category], T.Product, T.[Additional Info], T.[User ID], T.[User Contact Name and BP ID],
 T.Email, T.[Staff Number], T.[Work Order], T.[Asset Tag], T.[Job Recovery Code], T.[Service Start Date], 
T.[Service End Date], T.[Start Month], T.[End Month], T.[RFS Opener], T.[RFS Closer], 
T.[Previous Year Ref Figure (March)], T.April, T.May, T.June, T.July, T.August, T.September, 
T.October, T.November, T.December, T.January, T.February, T.March, T.[April Delta], 
T.[May Delta], T.[June Delta], T.[July Delta], T.[August Delta], T.[September Delta], 
T.[October Delta], T.[November Delta], T.[December Delta], T.[January Delta], T.[February Delta], T.[March Delta], Sum(T.[Year to Date]) 
FROM `D:\GRoup IS Billing\Group IS Billing`.`'Delta Report Data$'` T
Group By T.Segment, T.SBU, T.Division, T.Operation, T.[Business Unit],
 T.Team, T.[Product Category], T.Product, T.[Additional Info], T.[User ID], T.[User Contact Name and BP ID],
 T.Email, T.[Staff Number], T.[Work Order], T.[Asset Tag], T.[Job Recovery Code], T.[Service Start Date], 
T.[Service End Date], T.[Start Month], T.[End Month], T.[RFS Opener], T.[RFS Closer], 
T.[Previous Year Ref Figure (March)], T.April, T.May, T.June, T.July, T.August, T.September, 
T.October, T.November, T.December, T.January, T.February, T.March, T.[April Delta], 
T.[May Delta], T.[June Delta], T.[July Delta], T.[August Delta], T.[September Delta], 
T.[October Delta], T.[November Delta], T.[December Delta], T.[January Delta], T.[February Delta], T.[March Delta]

You need to include every field in the GROUP BY clause that isn't an aggregate function (ie the SUM() field is the only one you leave out of the group by). Also, whilst Excel records field names with spaces in ``, these often cause errors once a manual amendment is made to the SQL (hence I've replaced with [] brackets). Since you only have one table (ie there are no joins in the query) you don't actually need to prefix each field with the table name (ie `'Delta Report Data$'`) however, I have retained this, but used an alias of T instead (it's easier and shorter to read).

Hope this helps!

Richard

hI,

Thank u, that was too foolish of me. I need to sharpen my programing skills. thank u r a guru.

Good Day
Vaishak