Tim

I'm getting this error

ElevateDB Error #700 An error was found in the statement at line 2 and column 1 (Invalid expression "_Forename" found, non-aggregated column references not allowed)

with this query.

SELECT
_Forename+' '+_Surname+' ('+CAST(COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) AS VARCHAR(3))+')',
CAST(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16)) AS VARCHAR(80)),
_fkCompanies
FROM
Career
JOIN Contacts C ON _fkContacts = C._ID
GROUP BY _Surname,_Forename,_fkCompanies,_fkSites
HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1

I can have

_Forename+' '+_Surname

and

COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16)))

as separate columns but can't add them together.

To me it doesn't make sense. Can you explain please.

Roy Lambert

Roy,

<< I can have

_Forename+' '+_Surname

and

COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) +
CAST(_fkSites AS VARCHAR(16)))

as separate columns but can't add them together. >>

I don't understand what you mean by this statment - please show me the SQL
statements that you're using - the ones that work and the ones that don't.

In general, the error means that you can't use non-aggregated columns in the
SELECT list that aren't in the GROUP BY.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I don't understand what you mean by this statment - please show me the SQL
>statements that you're using - the ones that work and the ones that don't.

The one I posted doesn't work. This one

SELECT
_Forename+' '+_Surname,
CAST(COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) AS VARCHAR(3)),
CAST(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16)) AS VARCHAR(80)),
_fkCompanies
FROM
Career
JOIN Contacts C ON _fkContacts = C._ID
GROUP BY _Surname,_Forename,_fkCompanies,_fkSites
HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1

Does.

>In general, the error means that you can't use non-aggregated columns in the
>SELECT list that aren't in the GROUP BY.

It would appear that this is not general <vbg>

Roy Lambert

Roy,

You could use derived a derived table to get what you want.  Something like this should work.

SELECT
 _Surname + ' ' + _Forename + ' ' +
CAST(_fkCompanies AS VARCHAR(3)) +
CAST( _fkSites AS VARCHAR(3)) +
CAST(X1 AS VARCHAR(3))
  FROM (

SELECT
_Surname, _Forename, _fkCompanies, _fkSites,
CAST (COUNT(*) AS VARCHAR(3)) AS X1
     FROM Career C1
     GROUP BY _Surname, _Forename, _fkCompanies, _fkSites  )   AS T1

You could also replace
HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1

with HAVING COUNT(*) > 1 since you are grouping on the columns contained in the COUNT expression.

Richard Harding

Richard

>You could use derived a derived table to get what you want. Something like this should work.
>
>SELECT
> _Surname + ' ' + _Forename + ' ' +
> CAST(_fkCompanies AS VARCHAR(3)) +
> CAST( _fkSites AS VARCHAR(3)) +
> CAST(X1 AS VARCHAR(3))
> FROM (
>
>SELECT
> _Surname, _Forename, _fkCompanies, _fkSites,
> CAST (COUNT(*) AS VARCHAR(3)) AS X1
> FROM Career C1
> GROUP BY _Surname, _Forename, _fkCompanies, _fkSites ) AS T1

With some tweaking (to allow for table structure) that could work, but the original question still stands.

>You could also replace
>HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1
>
>with HAVING COUNT(*) > 1 since you are grouping on the columns contained in the COUNT expression.

Something else I didn't know.

Roy Lambert

Roy,

<< The one I posted doesn't work. This one >>

Yes, but you're not "combining" columns there - you have a separate
_Forename+' '+_Surname expression and then a separate CAST(COUNT())
expression.  The _Forename+' '+_Surname is not in the GROUP BY, so EDB is
complaining.

--
Tim Young
Elevate Software
www.elevatesoft.com

Richard,

<< You could use derived a derived table to get what you want. >>

As an aside, you'll be happy to know that 2.03 B10 finally fixes the global
derived table naming issues - you can now use whatever name you want and the
scope is local to the current SQL statement, not global for the entire
session.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Yes, but you're not "combining" columns there - you have a separate
>_Forename+' '+_Surname expression and then a separate CAST(COUNT())
>expression. The _Forename+' '+_Surname is not in the GROUP BY, so EDB is
>complaining.

I'm still lost (probably being thick).

"(Invalid expression "_Forename" found, non-aggregated column references not allowed"

Is what I'm told. But

GROUP BY _Surname,_Forename,_fkCompanies,_fkSites

So _Forename is in the GROUP BY. The COUNT expression isn't in the GROUP BY so is that what's really being complained about that I'm trying to catenate two items that are in the GROUP BY with one that isn't?

If so it still doesn't make sense to me. I can list the columns so why can't I combine the output into a single column?

Roy Lambert

Tim
> Yes, but you're not "combining" columns there - you have a separate
> _Forename+' '+_Surname expression and then a separate CAST(COUNT())
> expression.  The _Forename+' '+_Surname is not in the GROUP BY, so EDB is
> complaining.

Isn't it just the opposite (ie the _Forename+' '+_Surname was OK) ?

While I am not going to lose any sleep over it <bg> I can't see logically
why any column should not be referenced in the SELECT  list as long as it is
included in the GROUP BY clause.

ElevateDB allows the following

SELECT columna+columnb,count(*)+sum(columna)+sum(columnb) FROM Table
GROUP BY columna,columnb

but not

SELECT columna+columnb,count(*)+columna FROM Table
GROUP BY columna,columnb

John

Roy

As an aside to this, do you have any idea why OE would decide this thread
should be part of a conversation started by you on 26/0/5/2008?  I can't see
any link bar the subject is the same.  Strange......

John