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.
|||When I highlight a few partitions and start processing, the process occasionally stops with a message that operation has been canceled, like this:
DouglasL wrote: