Group by clause in XL

[Vaishak]

Hi Gals and Guys,

I am trying to import data into XL using data base query. I am trying to edit the SQL statement to group the data and do a subtotal of one of the column. But when i try this XL returns me a message popup " You tried to execute a query that doesnot include the expression 'Segment' as part of an aggregate function" with OK and Help buttons but he Help button doesnot return a blank page.


I would like to know if group by clause and the sum clause is supported in XL. And if any one can tell what the message in red text mean especially the term segment.

Thanks
Vaishak

[Richard Schollar]

Hi

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

Richard

[Vaishak]

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

[Richard Schollar]

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):

Please Login or Register  to view this content.

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

[Vaishak]

hI,

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

Good Day
Vaishak