Board index » delphi » Local SQL problem

Local SQL problem

How to SQL command (SUM)
Sel:
             WEI  UNIT
             ===  =====
              1.2      KG
             500      G
            =========
total:   1700      G

I am use Paradox

 

Re:Local SQL problem


Quote
"clipe" <cl...@ksts.seed.net.tw> wrote:
>How to SQL command (SUM)
>Sel:
>             WEI  UNIT
>             ===  =====
>              1.2      KG
>             500      G
>            =========
>total:   1700      G

>I am use Paradox

Select SUM(WEI) from TABLE

Smola
--
"And once again, the day is saved!"
(supersmola.tripod.com/index.htm}

Re:Local SQL problem


but my UNIT different
KG = WEI * 100
   G = WEI
===============
          TOTAL
TKS :-)
Quote
Smola <A...@inet.hr> wrote in message news:3a09f340.1810467@news.iskon.hr...
> "clipe" <cl...@ksts.seed.net.tw> wrote:

> >How to SQL command (SUM)
> >Sel:
> >             WEI  UNIT
> >             ===  =====
> >              1.2      KG
> >             500      G
> >            =========
> >total:   1700      G

> >I am use Paradox

> Select SUM(WEI) from TABLE

> Smola
> --
> "And once again, the day is saved!"
> (supersmola.tripod.com/index.htm}

Re:Local SQL problem


This works with Sybase Adaptive Server Anywhere. I'm not sure if it
also works with local SQL:

SELECT SUM(IF unit = 'KG' THEN WEI * 1000 ELSE WEI ENDIF) AS GramTotal
FROM table

Re:Local SQL problem


Thank your answer ! but trying with error message "invalid use keyword"

Quote
Mark Shapiro <info...@swbell.net> wrote in message

news:3rgl0tk240re1tef15qddts37cpr51idd9@4ax.com...
Quote
> This works with Sybase Adaptive Server Anywhere. I'm not sure if it
> also works with local SQL:

> SELECT SUM(IF unit = 'KG' THEN WEI * 1000 ELSE WEI ENDIF) AS GramTotal
> FROM table

Re:Local SQL problem


With Paradox, I guess you're out of luck and left with nothing else than
solve the problem in code (in your application).

BTW, with Oracle I could, but never would, as the problem resumes in fact to
bad db-design, imo.

Quote
clipe <cl...@ksts.seed.net.tw> wrote in message

news:8uasii$8hej4$1@news.ht.net.tw...
Quote
> How to SQL command (SUM)
> Sel:
>              WEI  UNIT
>              ===  =====
>               1.2      KG
>              500      G
>             =========
> total:   1700      G

> I am use Paradox

Re:Local SQL problem


You could create an extra field with the multiplication factor, e.g.:

ALTER TABLE "Table.db"
ADD FACTOR INTEGER

UPDATE "Table.db"
SET FACTOR=1
WHERE UNIT='G'

UPDATE "Table.db"
SET FACTOR=1000
WHERE UNIT='KG'

SELECT SUM(WEI*FACTOR)
FROM "Table.db"

"clipe" <cl...@ksts.seed.net.tw> schreef in bericht
news:8uasii$8hej4$1@news.ht.net.tw...

Quote
> How to SQL command (SUM)
> Sel:
>              WEI  UNIT
>              ===  =====
>               1.2      KG
>              500      G
>             =========
> total:   1700      G

> I am use Paradox

Re:Local SQL problem


Look at the IF command in your database documentation. Some require a
parenthesis after the IF, some don't require the ENDIF, etc.

SUM (IF(condition THEN usethisfield ELSE usethatfield))

But as far as I can see, your basic approach is sound and should work.

Robert

Quote
clipe <cl...@ksts.seed.net.tw> wrote in message

news:8ufol9$8o28l$1@news.ht.net.tw...
Quote
> Thank your answer ! but trying with error message "invalid use keyword"

> Mark Shapiro <info...@swbell.net> wrote in message
> news:3rgl0tk240re1tef15qddts37cpr51idd9@4ax.com...
> > This works with Sybase Adaptive Server Anywhere. I'm not sure if it
> > also works with local SQL:

> > SELECT SUM(IF unit = 'KG' THEN WEI * 1000 ELSE WEI ENDIF) AS GramTotal
> > FROM table

Re:Local SQL problem


Quote
"clipe" <cl...@ksts.seed.net.tw> wrote:
>but my UNIT different
>KG = WEI * 100
>   G = WEI
>===============
>          TOTAL
>TKS :-)

Sorry, I didn't see it.

You can use UNION:

SELECT SUM(wei) FROM table WHERE units = "G"
UNION ALL
SELECT SUM(wei * 1000) FROM table WHERE units = "KG"

This will give you a table with two records so the total sum is

with table do begin
        first;
        sum := Fields[0].AsInteger;
        next;
        sum := sum + Fields[0].AsInteger;
end;

Smola
--
"And once again, the day is saved!"
(supersmola.tripod.com/index.htm}

Re:Local SQL problem


THANK YOU VERY MUCH FOR YOUR HELP
Quote
Smola <A...@inet.hr> wrote in message news:3a0be960.6378691@news.iskon.hr...
> "clipe" <cl...@ksts.seed.net.tw> wrote:

> >but my UNIT different
> >KG = WEI * 100
> >   G = WEI
> >===============
> >          TOTAL
> >TKS :-)

> Sorry, I didn't see it.

> You can use UNION:

> SELECT SUM(wei) FROM table WHERE units = "G"
> UNION ALL
> SELECT SUM(wei * 1000) FROM table WHERE units = "KG"

> This will give you a table with two records so the total sum is

> with table do begin
> first;
> sum := Fields[0].AsInteger;
> next;
> sum := sum + Fields[0].AsInteger;
> end;

> Smola
> --
> "And once again, the day is saved!"
> (supersmola.tripod.com/index.htm}

Other Threads