Total Weight - How :(

[daint]

Hello,

I've got possibly a simple question, but I've gone from not knowing any SQL to trying to go 'advanced' in about a week.

I have a table with Jobno, Prefix and PackID along with weight.

So each parcel contains a weight, these are added to the table via being scanned. There are no checks when entering into the table, and there can't be.

The problem I have is I need get a sum of all the weights.

I did use
"SELECT SUM(Weight) as 'Total Weight' FROM `Labelfiles` WHERE `date_stamp` = <TODAYSDATE>"

and this works fine, however, I've now been asked to ignore the duplicate scans.

I could probably do this using PHP variables, but would be demanding on a 166Mhz Unix machine when there are 100000+ entries.

I was wondering how I could alter my code to take this into account.

The problem is the PackID may not be unique, nor the prefix and jobno, but all three together will be (unless a duplicate scan has been made)

Any ideas?

 

[bborissov]

What do you mean with "duplicate scans"?
1. You want the total weight for these parcels that have different weight?
2. You want total weight for non duplicate parcels?

Borislav Borissov

 

[daint]

The second, sorry

I suppose I'm really looking for a unique combinations of
Jobno & Prefix & PackID

which is the way I'd have probably approached it using PHP

 

[bborissov]

SELECT SUM(Weight) FROM
 (SELECT Jobno, Prefix, PackID, MAX(Weight) AS Weight
         FROM MyTable
         GROUP BY Jobno, Prefix, PackID) B

Borislav Borissov

 

[gmmastros]

I suppose that the weight would be the same also. Meaning, if there is a duplicate jobno, prefix and PackId, then the weight would be the same too.

If this is true, then you could probably use a subquery to accomplish this.

SELECT SUM(Weight) as 'Total Weight' FROM `Labelfiles` WHERE `date_stamp` = <TODAYSDATE>"

Select Sum(Weight) As [Total Weight]
From   (
       Select Distinct
              JobNo,
              Prefix,
              PackId,
              Weight
       From   LabelFiles
       Where  Date_Stamp = <TODAYSDATE>
       ) As A

If this is NOT what you are looking for, then please post some sample data and expected results, and we will help further.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Btw. that requires mySQL 4.1 or higher .
(blind guess of course).

Yup, the question is: what if there are "duplicate" scans but with different weights. Which one to consider: earliest, latest, smallest, largest value? And this is hard to guess without exact samples + results.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]