Board index » delphi » SQL PROBLEM !!! PLEASE HELP

SQL PROBLEM !!! PLEASE HELP

HI
i've a problem with SQL:

SELECT DCLIENTS.NAME,SUM(PAYMENTS.SUM_PAY)
FROM DCLIENTS,PAYMENTS,DACCOUNTS
WHERE (PAYMENTS.ACCOUNT_ID = DACCOUNTS.ACCOUNT_ID) AND
(DACCOUNTS.CLIENT_ID = DCLIENTS.CLIENT_ID)
GROUP BY NAME
HAVING MIN(NAME) > 'A' AND MAX(NAME ) < 'B'

this code gives back the SUM(PAYMENTS.SUMPAY) of all clients with a
name starting with 'A' ... that's fine .. but i need to get the
SUM(PAYMENTS.SUMPAY) of alls names starting with 'B' .. 'Z', too ..
do i need to program a queue ??? PLEASE HELP MEEEE !!!!!
and how could i code the MIN(NAME) > 'A' AND MAX(NAME) < 'B' in a
better way ???

 

Re:SQL PROBLEM !!! PLEASE HELP


Quote
>SELECT DCLIENTS.NAME,SUM(PAYMENTS.SUM_PAY)
>FROM DCLIENTS,PAYMENTS,DACCOUNTS
>WHERE (PAYMENTS.ACCOUNT_ID = DACCOUNTS.ACCOUNT_ID) AND
>(DACCOUNTS.CLIENT_ID = DCLIENTS.CLIENT_ID)
>GROUP BY NAME
>HAVING MIN(NAME) > 'A' AND MAX(NAME ) < 'B'

>this code gives back the SUM(PAYMENTS.SUMPAY) of all clients with a
>name starting with 'A' ... that's fine .. but i need to get the
>SUM(PAYMENTS.SUMPAY) of alls names starting with 'B' .. 'Z', too ..
>do i need to program a queue ??? PLEASE HELP MEEEE !!!!!
>and how could i code the MIN(NAME) > 'A' AND MAX(NAME) < 'B' in a
>better way ???

Try -

  GROUP BY SUBSTRING(NAME, 1, 1)

You might also like to improve performance on this query by overspecifying
the WHERE clause (it helps the optimiser) -

AND (PAYMENTS.ACCOUNT_ID = DCLIENTS.CLIENT_ID)

I really don't know what you mean by a queue, a loop maybe, if so you don't
need one.

Regards
--
Andrew Prosser

Re:SQL PROBLEM !!! PLEASE HELP


You could use wildcard characters :
%               Any string of zero or more characters
_ (underscore)  Any single character
[ ]             Any single character within the specified range ([a-f]) or
set ([abcdef])
[^]             Any single character not within the specified range ([^a-f])
or set ([^abcdef])

Your Where clause would have an added statement of "name Like '[A-Z]%'", the
Having clause can be removed.

In article <6hvapf$5o...@clubserv.rp-online.de>,

Quote
  CIM...@gmx.net wrote:

> HI
> i've a problem with SQL:

> SELECT DCLIENTS.NAME,SUM(PAYMENTS.SUM_PAY)
> FROM DCLIENTS,PAYMENTS,DACCOUNTS
> WHERE (PAYMENTS.ACCOUNT_ID = DACCOUNTS.ACCOUNT_ID) AND
> (DACCOUNTS.CLIENT_ID = DCLIENTS.CLIENT_ID)
> GROUP BY NAME
> HAVING MIN(NAME) > 'A' AND MAX(NAME ) < 'B'

> this code gives back the SUM(PAYMENTS.SUMPAY) of all clients with a
> name starting with 'A' ... that's fine .. but i need to get the
> SUM(PAYMENTS.SUMPAY) of alls names starting with 'B' .. 'Z', too ..
> do i need to program a queue ??? PLEASE HELP MEEEE !!!!!
> and how could i code the MIN(NAME) > 'A' AND MAX(NAME) < 'B' in a
> better way ???

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/   Now offering spam-free web-based newsreading

Re:SQL PROBLEM !!! PLEASE HELP


i think, you don't understand my problem ..
i have to build groups .. one for each letter of the alphabet  ..

A-CLIENTS, B-CLIENTS, C-CLIENTS ...

for each group, i've to sum() an integer value

         Value
Ab...   20                        Bb.... 40
Ac...   10                        Ba.... 50

A-CLIENTS = 30           B-CLIENTS = 90

select *
 from dclients
where ....
group by SUBSTRING(name,from 1 for 1)

doesn't work ?! .(errormessage)
substring(name,1,1), too

any ideas ?

Re:SQL PROBLEM !!! PLEASE HELP


Quote
CIM...@gmx.net wrote:

>i think, you don't understand my problem ..

Or, maybe, you had not expressed it clearly enough...........
Quote
>i have to build groups .. one for each letter of the alphabet  ..

>A-CLIENTS, B-CLIENTS, C-CLIENTS ...

>for each group, i've to sum() an integer value

>         Value
>Ab...   20                        Bb.... 40
>Ac...   10                        Ba.... 50

>A-CLIENTS = 30           B-CLIENTS = 90

SELECT  SUBSTRING(DCCLIENTS.NAME,1,1),
DCLIENTS.NAME,SUM(PAYMENTS.SUM_PAY)
        FROM DCLIENTS,PAYMENTS,DACCOUNTS
WHERE (PAYMENTS.ACCOUNT_ID = DACCOUNTS.ACCOUNT_ID) AND
(DACCOUNTS.CLIENT_ID = DCLIENTS.CLIENT_ID)
GROUP BY SUBSTRING(DCCLIENTS.NAME,1,1),NAME
WITH ROLLUP

Is that what you need?  You have not been clear enough this time
either...

Have fun!!!

Isaac

Re:SQL PROBLEM !!! PLEASE HELP


If you see an error message, do you just give up, no wonder MS gets such a
bad rap sometimes, why don't you try this;

select title_id, price from titles

select substring(title_id, 1, 1), sum(price) from titles
group by substring(title_id, 1, 1)

title_id price
-------- --------------------------
BU1032   19.99
BU1111   11.95
BU2075   2.99
BU7832   19.99
MC2222   19.99
MC3021   2.99
MC3026   (null)
PC1035   22.95
PC8888   20.00
PC9999   (null)
PS1372   21.59
PS2091   10.95
PS2106   7.00
PS3333   19.99
PS7777   7.99
TC3218   20.95
TC4203   11.95
TC7777   14.99

(18 row(s) affected)

- --------------------------
B 54.92
M 22.98
P 110.47
T 47.89

(4 row(s) affected)

Quote
CIM...@gmx.net wrote in message <6i2usr$j0...@clubserv.rp-online.de>...

>i think, you don't understand my problem ..
>i have to build groups .. one for each letter of the alphabet  ..

>A-CLIENTS, B-CLIENTS, C-CLIENTS ...

>for each group, i've to sum() an integer value

>         Value
>Ab...   20                        Bb.... 40
>Ac...   10                        Ba.... 50

>A-CLIENTS = 30           B-CLIENTS = 90

>select *
> from dclients
>where ....
>group by SUBSTRING(name,from 1 for 1)

>doesn't work ?! .(errormessage)
>substring(name,1,1), too

>any ideas ?

Re:SQL PROBLEM !!! PLEASE HELP


Hi!

your wrong query is here:

Quote
>HI
>i've a problem with SQL:

>SELECT DCLIENTS.NAME,SUM(PAYMENTS.SUM_PAY)
>FROM DCLIENTS,PAYMENTS,DACCOUNTS
>WHERE (PAYMENTS.ACCOUNT_ID = DACCOUNTS.ACCOUNT_ID) AND
>(DACCOUNTS.CLIENT_ID = DCLIENTS.CLIENT_ID)
>GROUP BY NAME
>HAVING MIN(NAME) > 'A' AND MAX(NAME ) < 'B'

you want to get sum of names starting with 'B'....'A':
The query should be as follows:

SELECT DCLIENTS.NAME,SUM(PAYMENTS.SUM_PAY)
FROM DCLIENTS,PAYMENTS,DACCOUNTS
WHERE (PAYMENTS.ACCOUNT_ID = DACCOUNTS.ACCOUNT_ID) AND
(DACCOUNTS.CLIENT_ID = DCLIENTS.CLIENT_ID)
GROUP BY NAME
HAVING NAME NOT LIKE 'A%'

I am sure this will solve ur problem.  If not contact me at
pala...@hotmail.com

PALANI P

Other Threads