query

[subs]

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20000 450
C D 4/1/08 15000 890
E F 9/1/08 18000 750
A B 12/1/07 1290 789
X Y 1/1/08 1700 900
C D 4/1/08 34000 90

The above table is called as freight. It has hundreds of records - a
sample is present above. i want to write a query which can select
those records which have same ozip, dzip and ship date. In the above
example, the query should extract first , fifth , second and sixth
records. Can somebody help me with such a query

Thanks for any help

 

[Ken Sheridan]

You can use a subquery to identify those rows where there is more than one
row with matching ozip, dzip and shipdate values and restrict the outer
query's result set to those rows:

SELECT *
FROM YourTable AS T1
WHERE
(SELECT COUNT(*)
FROM YourTable As T2
WHERE T2.ozip = T1.ozip
AND T2.dzip = T1.dzip
AND T2.shipdate = T1.shipdate) > 1;

Ken Sheridan
Stafford, England

 

[subs]

You can use a subquery to identify those rows where there is more than one
row with matching   ozip, dzip and shipdate values and restrict the outer
query's result set to those rows:

SELECT *
FROM YourTable AS T1
WHERE
    (SELECT COUNT(*)
      FROM YourTable As T2
      WHERE T2.ozip = T1.ozip
      AND T2.dzip = T1.dzip
      AND T2.shipdate = T1.shipdate) > 1;

Ken Sheridan
Stafford, England







THanks Canl you please help me on this as well
- Show quoted text -

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20 450
C D 4/1/08 15 890
E F 9/1/08 78 750
A B 12/1/07 42 789
X Y 1/1/08 17 900
C D 4/1/08 34 90

Needed Output

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20
450
A B 12/1/07 42 789
E F 9/1/08 78
750

I need to create a query which extracts rows with wgt >60 and also
when the fields ozip, dzip and shipdate are same, the query should add
the wgts and then sum of wgts should be >60- for example the first and
fifth rows should be extracted since sum is 62.

I have attached the output that is required for the above example.
please help

 

[Ken Sheridan]

You should be able to do that by changing the subquery, in this case summing
the wgt column. Whether there is only one row or multiple rows with the
matching values the subquery will return the total weight, so those rows will
be returned by the outer query if the total weight is more than 60.

SELECT *
FROM YourTable AS T1
WHERE
(SELECT SUM(wgt)
FROM YourTable As T2
WHERE T2.ozip = T1.ozip
AND T2.dzip = T1.dzip
AND T2.shipdate = T1.shipdate) > 60;

Ken Sheridan
Stafford, England