Board index » delphi » SQL need HELP: SELECT PROBLEM 1

SQL need HELP: SELECT PROBLEM 1

I have a table like

  NAME   PET1  PET2   QTY
  JOHN   DOG   CAT      5
  PAUL   CAT   LION    10
  PETE   MICE           2

I want to do an SQL statement which return

   PET    SUM(QTY)
   DOG     5
   CAT    15
   LION   10
   MICE    2

With Delphi 3.0's Quick Report.
I put a TQuery item there but I don't know what to put to the SQL
statement to make it work.
Experts please help.
Million Thanks.

 

Re:SQL need HELP: SELECT PROBLEM 1


Quote
On Thu, 5 Mar 1998 12:56:26 +0800, ae...@aelhk.com (aelhk) wrote:
>I have a table like

>  NAME   PET1  PET2   QTY
>  JOHN   DOG   CAT      5
>  PAUL   CAT   LION    10
>  PETE   MICE           2

>I want to do an SQL statement which return

>   PET    SUM(QTY)
>   DOG     5
>   CAT    15
>   LION   10
>   MICE    2

>With Delphi 3.0's Quick Report.
>I put a TQuery item there but I don't know what to put to the SQL
>statement to make it work.
>Experts please help.

Well, the following select will do the trick, though it relies on the
isnull function to fix those nulls, and assumes that the database that
you are using allows sub-selects.

select m.Pet1, sum(m.Qty) + isnull((select sum(t.Qty) from MyTable t
where t.Pet2 = m.Pet1),0)
from MyTable m
group by Pet1
union
select m.Pet2, sum(m.Qty)
from MyTable m
where m.Pet2 not in (select Pet1 from MyTable)
group by Pet2

This is far from an ideal solution, though: it uses a union, requires
sub-selects and the isnull function. You are probably better off doing
two group queries and adding the results in the application. A
re-think of the database design, e.g. splitting the pets into a
seperate table would yield more efficient access.

a simple e.g.

create table Person (
  PersonKey integer,
  Name char(20)
)

create table Pet (
  PersonKey integer,
  PetNumber integer,
  Qty integer
)

You could then easily count the pets belonging to a person using a
join.

Regards,

Mark Cummins
Cape Town, South Africa
EMail: g...@iafrica.com

"The gods too are fond of a joke."
    -- Aristotle

Other Threads