ERROR using GROUP BY

Hi guys,

The code below works fine if I take the "GROUP BY" Sentence out.

Without the group by, I am getting something that looks like:

Proj Nr Proj Name Proj Nr & Name

001 Proj 1 001 - Proj 1

001 Proj 1 001 - Proj 1

001 Proj 1 001 - Proj 1

002 Proj 2 002 - Proj 2

002 Proj 2 002 - Proj 2

002 Proj 2 002 - Proj 2

And I need only one line for each Proj, like:

001 Proj 1 001 - Proj 1

002 Proj 2 002 - Proj 2

(It is not because of the tables. It is because the Nr "001" is only a substring of the data)

I thought I can use GROUP BY in order to get only one line for each Proj.

This is the code I am trying:

SELECT

CAST(SUBSTRING(Accounts.ACCOUNTKEY, 1, 3) as char(3))

+ ' - ' +

CAST(Proj_Name.NOTE as char(15)) AS 'Proj Nr & Name',

Proj_Name.NOTE AS 'Proj Name',

SUBSTRING (Accounts.ACCOUNTKEY, 1, 3) AS 'Proj Nr'

FROM myCompany.dbo.ACCOUNTS Accounts

LEFT OUTER JOIN myCompany.dbo.EXTRANOTES Proj_Name ON Accounts.ACCOUNTKEY = Proj_Name.KEF AND Proj_Name.NOTEID In (17)

WHERE (Accounts.SORTGROUP Between 0 And 379 AND Proj_Name.NOTEID In (17))

ORDER BY SUBSTRING(Accounts.ACCOUNTKEY, 1, 3)

GROUP BY SUBSTRING(Accounts.ACCOUNTKEY, 1, 3)

Any help will be appreciatted.

Aldo.order by comes after group by|||Still getting ERROR...

SELECT

CAST(SUBSTRING(Accounts.ACCOUNTKEY, 1, 3) as char(3))

+ ' - ' +

CAST(Proj_Name.NOTE as char(15)) AS 'Proj Nr & Name',

Proj_Name.NOTE AS 'Proj Name',

SUBSTRING(Accounts.ACCOUNTKEY, 1, 3) AS 'Proj Nr'

FROM myCompany.dbo.ACCOUNTS Accounts

LEFT OUTER JOIN myCompany.dbo.EXTRANOTES Proj_Name

ON Accounts.ACCOUNTKEY = Proj_Name.KEF

AND Proj_Name.NOTEID In (17)

WHERE (Accounts.SORTGROUP Between 0 And 379

AND Proj_Name.NOTEID In (17))

GROUP BY SUBSTRING(Accounts.ACCOUNTKEY, 1, 3)

ORDER BY SUBSTRING(Accounts.ACCOUNTKEY, 1, 3)|||

wats the error...?

also include all non aggregate items - used in select - in the group by list...

|||It works right wihtout the Group By, but it shows the same line several times, like:

001 Proj1

001 Proj1

001 Proj1

instead of showing the data only once, like:

001 Proj1

Aldo.|||

i think just a distinct will work here for u...

select distinct....

as group by is used when u want to use aggregates etc, grouping by some columns....

--btw i was askin wats the error it gives when u use Group By...

|||SQL Syntax Error|||

SELECT

CAST(SUBSTRING(Accounts.ACCOUNTKEY, 1, 3) as char(3))

+ ' - ' +

CAST(Proj_Name.NOTE as char(15)) AS 'Proj Nr & Name',

Proj_Name.NOTE AS 'Proj Name',

SUBSTRING (Accounts.ACCOUNTKEY, 1, 3) AS 'Proj Nr'

FROM

ACCOUNTS Accounts

LEFT OUTER JOIN

EXTRANOTES Proj_Name

ON

Accounts.ACCOUNTKEY = Proj_Name.KEF AND Proj_Name.NOTEID In (17)

WHERE

(Accounts.SORTGROUP Between 0 And 379 AND Proj_Name.NOTEID In (17))

GROUP BY

SUBSTRING(Accounts.ACCOUNTKEY, 1, 3),note

ORDER BY

SUBSTRING(Accounts.ACCOUNTKEY, 1, 3)

|||Thanks man, I tried that but it doesn't work.|||

Please post the schema for the two tables.

Rich

www.richreeves.com

|||Guys, this is a simple one!

You're referring to other fields in your query, which are neither aggregates nor part of the 'group by' clause.

When you group by various elements, those elements become the only non-aggregated fields you can refer to in the select statement. So perhaps you need to include the NOTE field as well in the group by clause, like this:

GROUP BY SUBSTRING(Accounts.ACCOUNTKEY, 1, 3), Proj_Name.NOTE

This might still give an error because you're trying to group by only the first three characters of AccountKey, and then doing some more to it. So if you get an error when you try just changing the group by statement, then perhaps try changing the query a little more like this:

select
CAST([Proj Nr] as char(3))

+ ' - ' +

CAST([Proj Name] as char(15)) AS 'Proj Nr & Name',
*
FROM

(
SELECT
Proj_Name.NOTE AS 'Proj Name',
SUBSTRING (Accounts.ACCOUNTKEY, 1, 3) AS 'Proj Nr'
FROM myCompany.dbo.ACCOUNTS Accounts
LEFT OUTER JOIN myCompany.dbo.EXTRANOTES Proj_Name ON Accounts.ACCOUNTKEY = Proj_Name.KEF AND Proj_Name.NOTEID In (17)
WHERE (Accounts.SORTGROUP Between 0 And 379 AND Proj_Name.NOTEID In (17))
GROUP BY SUBSTRING(Accounts.ACCOUNTKEY, 1, 3), Proj_Name.NOTE
) p
ORDER BY [Proj Nr]

All I've done here is to simplify the bit where the GROUP BY applies, and then wrapped it up in a table expression (which I've aliased 'p'). You can then refer to the other fields more easily in the SELECT and ORDER BY clauses.

See how you go though. By all means post what's wrong with this one. :)

Rob|||select
CAST([Proj Nr] as char(3))

+ ' - ' +

CAST([Proj Name] as char(15)) AS 'Proj Nr & Name',
*
FROM

(
SELECT
Proj_Name.NOTE AS 'Proj Name',
SUBSTRING (Accounts.ACCOUNTKEY, 1, 3) AS 'Proj Nr'
FROM myCompany.dbo.ACCOUNTS Accounts
LEFT OUTER JOIN myCompany.dbo.EXTRANOTES Proj_Name ON Accounts.ACCOUNTKEY = Proj_Name.KEF AND Proj_Name.NOTEID In (17)
WHERE
( Accounts.SORTGROUP Between 0 And 379
-- a bit redundant isn't it ?
-- AND Proj_Name.NOTEID In (17)
)
GROUP BY SUBSTRING(Accounts.ACCOUNTKEY, 1, 3), Proj_Name.NOTE
) p
ORDER BY [Proj Nr]|||Yeah, sure... but I was just using the original query, not trying to change the logic of it.|||Hi Guys,

It works just Great, THANKS A LOT!

Aldo.