Board index » delphi » GROUP BY Expression

GROUP BY Expression


2006-12-20 09:26:00 PM
delphi98
Hi,
Is There a way to do this Query????
SELECT Concat(Col1,Col2), SUM(Col3)
FROM Table1
GROUP BY Concat(Col1,Col2)
Thanks
 
 

Re:GROUP BY Expression

try this,
SELECT Col1 || Col2, SUM(Col3)
FROM Table1
GROUP BY 1
"Bernardo" <XXXX@XXXXX.COM>, haber iletisinde unlar?
yazd?XXXX@XXXXX.COM...
Quote
Hi,
Is There a way to do this Query????

SELECT Concat(Col1,Col2), SUM(Col3)
FROM Table1
GROUP BY Concat(Col1,Col2)

Thanks

 

Re:GROUP BY Expression

Quote

SELECT Col1 || Col2, SUM(Col3)
FROM Table1
GROUP BY 1
Won't work. ORDER BY <ordinal>works though.
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
www.upscene.com
My thoughts:
blog.upscene.com/martijn/
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:GROUP BY Expression

Bernardo writes:
Quote
Is There a way to do this Query????

SELECT Concat(Col1,Col2), SUM(Col3)
FROM Table1
GROUP BY Concat(Col1,Col2)
SELECT Col1 || Col2, SUM(Col3)
FROM Table1
GROUP BY Col1,Col2
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The legitimate powers of government extend to such acts only as are
injurious to others. But it does me no injury for my neighbor to say
there are twenty gods, or no God. It neither picks my pocket nor breaks
my leg." ?Thomas Jefferson
 

Re:GROUP BY Expression

Thanks Wayne,
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>escribi?en el mensaje
Quote
Bernardo writes:
>Is There a way to do this Query????
>
>SELECT Concat(Col1,Col2), SUM(Col3)
>FROM Table1
>GROUP BY Concat(Col1,Col2)

SELECT Col1 || Col2, SUM(Col3)
FROM Table1
GROUP BY Col1,Col2

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The legitimate powers of government extend to such acts only as are
injurious to others. But it does me no injury for my neighbor to say
there are twenty gods, or no God. It neither picks my pocket nor breaks
my leg." - Thomas Jefferson

 

Re:GROUP BY Expression

Now... Wayne this doesn't work
SELECT Col1 || ' - ' || Col2, SUM(Col3)
FROM Table1
GROUP BY Col1,' - ', Col2
any idea
Thanks
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>escribi?en el mensaje
Quote
Bernardo writes:
>Is There a way to do this Query????
>
>SELECT Concat(Col1,Col2), SUM(Col3)
>FROM Table1
>GROUP BY Concat(Col1,Col2)

SELECT Col1 || Col2, SUM(Col3)
FROM Table1
GROUP BY Col1,Col2

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The legitimate powers of government extend to such acts only as are
injurious to others. But it does me no injury for my neighbor to say
there are twenty gods, or no God. It neither picks my pocket nor breaks
my leg." - Thomas Jefferson

 

Re:GROUP BY Expression

Bernardo writes:
Quote

SELECT Col1 || ' - ' || Col2, SUM(Col3)
FROM Table1
GROUP BY Col1,' - ', Col2
Group by can only work on actual fields, it makes no sense to group by a
literal.
If you really need to have a single group by on those two fields, then
define a view that creates this as a virtual field and then run a query
against that.
The view would be:
create view XXX (
Col1and2, Col3
) as
select Col1 || '-' || Col2, Col3 from table1;
Now your query can be
select Col1and2, Sum(col3)
from XXX
group by Col1and2
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
 

Re:GROUP BY Expression

thanks a lot
wayne
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>escribi?en el mensaje
Quote
Bernardo writes:
>
>SELECT Col1 || ' - ' || Col2, SUM(Col3)
>FROM Table1
>GROUP BY Col1,' - ', Col2

Group by can only work on actual fields, it makes no sense to group by a
literal.

If you really need to have a single group by on those two fields, then
define a view that creates this as a virtual field and then run a query
against that.

The view would be:

create view XXX (
Col1and2, Col3
) as
select Col1 || '-' || Col2, Col3 from table1;

Now your query can be

select Col1and2, Sum(col3)
from XXX
group by Col1and2

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.