aggregating query help, please...

[admoore]

I need to aggregate a sum by primary key(an acct number); however, I need to REPEAT that result for multiple SERIAL NUMBERS(vins) each associated with that acct number-

i.e. if two serial numbers are associated with a single account, I need 2 lines in the output with the same sum...

related tables:

CREATE TABLE users_vins (
  vin varchar(17) NOT NULL default '',
  acct_no int(11) NOT NULL default '0',
  reg_date date NOT NULL default '0000-00-00',
  PRIMARY KEY  (vin)
)
CREATE TABLE points_service (
  trans_no int(11) NOT NULL auto_increment,
  post_date date NOT NULL default '0000-00-00',
  acct_no int(11) NOT NULL default '0',
  emp_no int(11) NOT NULL default '0',
  type_code varchar(12) NOT NULL default '',
  vin varchar(17) NOT NULL default '',
  description tinytext NOT NULL,
  points int(11) NOT NULL default '0',
  PRIMARY KEY  (trans_no),
  UNIQUE KEY type_code (type_code)
)

This query WILL give me a correct result; however, only for a single serial # (vin) per account number:

SELECT `acct_no` , vin, Sum( points ) AS Total
FROM points_service
GROUP BY acct_no

I had hoped this join query would give me a correct result by serial number(vin); however, each row returns the sume for the entire table- not what I want:

SELECT users_vins.acct_no, users_vins.vin, Sum( points_service.points ) AS Total
FROM users_vins
INNER JOIN points_service ON users_vins.acct_no = users_vins.acct_no
GROUP BY vin

Any help in correcting the query to get the desired result would be greatly appreciated!

Thanks in advance,

-Allen M

 

[r937]

GROUP BY acct_no , vin



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[admoore]

tried GROUP BY acct_no , vin; but, the
Sum( points_service.points ) AS Total

is still the sum of ALL records, not just those for unique acct_no's

i.e. all records return the same (incorrect)Total

-A

 

[admoore]

Silly me-

Turns out my prob was the line:
users_vins.acct_no = users_vins.acct_no

Oops, MyBad.

Thanks for the help!

-A