Board index » delphi » Help needed with SQL Select

Help needed with SQL Select

I have a table with columns 'customer' and 'amount', I want to perform a
SELECT
that will display each of the customers once and a sum of amounts for that
customer.
I tried this:
SELECT DISTINCT CUSTOMER, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER, AMOUNT

I have this:

cutomer   amount
-----------------
cust1      10
cust1       3
cust2      12

I want this:

customer  amount
----------------
cust1      13
cust2      12

Thanks for your time

-Danny-

 

Re:Help needed with SQL Select


If you drop the "distinct" and the "group by", I think you should be OK.

I haven't verified it, but that's from the hip.

HT

Quote
Danny wrote:
> I have a table with columns 'customer' and 'amount', I want to perform a
> SELECT
> that will display each of the customers once and a sum of amounts for that
> customer.
> I tried this:
> SELECT DISTINCT CUSTOMER, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER, AMOUNT

> I have this:

> cutomer   amount
> -----------------
> cust1      10
> cust1       3
> cust2      12

> I want this:

> customer  amount
> ----------------
> cust1      13
> cust2      12

> Thanks for your time

> -Danny-

Re:Help needed with SQL Select


You're right, dropping the DISTINCT works.

I tried the following:

"select customer, sum(amount) from tablename group by customer"

Quote
Hunter Trumbo wrote:
> If you drop the "distinct" and the "group by", I think you should be OK.

> I haven't verified it, but that's from the hip.

> HT

> Danny wrote:

> > I have a table with columns 'customer' and 'amount', I want to perform a
> > SELECT
> > that will display each of the customers once and a sum of amounts for that
> > customer.
> > I tried this:
> > SELECT DISTINCT CUSTOMER, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER, AMOUNT

> > I have this:

> > cutomer   amount
> > -----------------
> > cust1      10
> > cust1       3
> > cust2      12

> > I want this:

> > customer  amount
> > ----------------
> > cust1      13
> > cust2      12

> > Thanks for your time

> > -Danny-

Re:Help needed with SQL Select


Drop the Amount in the Group By clause.

elio

Re:Help needed with SQL Select


Try

SELECT  CUSTOMER, SUM(AMOUNT) FROM TABLE
GROUP BY CUSTOMER

Bill

--
Bill Todd
(Sorry but TeamB cannot answer questions received via email)
(Remove nospam from my email address to contact me for any other reason)

Re:Help needed with SQL Select


That's correct, and works if I select only these two columns,
but the problem is in that I need to select some other columns too.
When I add other columns to SELECT statement:

SELECT CUSTOMER, C1, C2, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER,
C1,C2,AMOUNT

 I get this error message:

'When GROUP BY exists, every simple field in projectors must be in GROUP BY'

If I add other columns to GROUP BY:

SELECT CUSTOMER, C1, C2, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER,
C1,C2,AMOUNT

I get duplicate records.

-Danny-

Quote
Praghna Solanki Peterson wrote in message

<36671DFE.19F7D...@gw.futuresource.com>...
Quote
>You're right, dropping the DISTINCT works.

>I tried the following:

>"select customer, sum(amount) from tablename group by customer"

>Hunter Trumbo wrote:

>> If you drop the "distinct" and the "group by", I think you should be OK.

>> I haven't verified it, but that's from the hip.

>> HT

>> Danny wrote:

>> > I have a table with columns 'customer' and 'amount', I want to perform
a
>> > SELECT
>> > that will display each of the customers once and a sum of amounts for
that
>> > customer.
>> > I tried this:
>> > SELECT DISTINCT CUSTOMER, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER,
AMOUNT

>> > I have this:

>> > cutomer   amount
>> > -----------------
>> > cust1      10
>> > cust1       3
>> > cust2      12

>> > I want this:

>> > customer  amount
>> > ----------------
>> > cust1      13
>> > cust2      12

>> > Thanks for your time

>> > -Danny-

Re:Help needed with SQL Select


Quote
>but the problem is in that I need to select some other columns too.
>When I add other columns to SELECT statement:

>SELECT CUSTOMER, C1, C2, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER,
>C1,C2,AMOUNT

> I get this error message:

>'When GROUP BY exists, every simple field in projectors must be in GROUP

BY'

When you use aggregate functions (SUM, COUNT, etc), then all other fields in
your select must be in the GROUP BY to get a correct result. If you think
about it for a minute, you'll see that it has to be that way. It can be
frustrating, but it's imposed by SQL, not by Delphi or the BDE.

Re:Help needed with SQL Select


Quote
On Fri, 4 Dec 1998 00:49:27 +0100, "Danny" <da...@eunet.yu> wrote:
>That's correct, and works if I select only these two columns,
>but the problem is in that I need to select some other columns too.
>When I add other columns to SELECT statement:

>SELECT CUSTOMER, C1, C2, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER,
>C1,C2,AMOUNT

> I get this error message:

>'When GROUP BY exists, every simple field in projectors must be in GROUP BY'

>If I add other columns to GROUP BY:

>SELECT CUSTOMER, C1, C2, SUM(AMOUNT) FROM TABLE GROUP BY CUSTOMER,
>C1,C2,AMOUNT

>I get duplicate records.

As John Provine posted, this is the behavior of SQL. If a column is not the
one or one of the columns on which the group is based, and is not being
summarized, there is no practical way to determine which value from the
other columns actually needs to be included in the result set for a group.

Take the example data below for example:

  Customer  Amount  C1
  --------  ------  --
  ABC        99.95   a
  ABC        29.99   b
  XYZ        10.49   c
  XYZ        10.49   d

The SQL statement below is along the lines of what you are describing:
grouping on Customer, summarizing Amount, and simply including C1.

  SELECT Customer, SUM(Amount), C1
  FROM Table1
  GROUP BY Customer

This would attempt to produce one aggregated row in the result set for each
value in Customer (here, two groups: one each for ABC and XYZ). The values
in the Amount column would be totaled for each group (129.94 and 20.98).
But for the C1 column, which values would be -- should be -- included? For
the group ABC, should it be "a" or "b"? No real way to determine that in
SQL.

And if you were to include C1 in the GROUP BY clause, there would be four
distinct combinations of values for the Customer and C1 columns, producing
four groups: ABC and a, ABC and b, XYZ and c, and XYZ and d. Not what you
wanted either.

So in an aggregating query, columns that are neither the basis for grouping
nor themselves aggregated cannot be included in such a query. These
individual values would require a non-aggregating query to view.

One way you might handle this is to display the aggregated result set on a
form. Provide some means there to initiate a second query, such as a button
or a double-click handler in a grid. Use the Customer column value for the
current row in this aggregated result set to filter a second query, to be
displayed in another form. That second query would not group or aggregate;
just show the individual values in rows corresponding to a particular
Customer.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham

Other Threads