Using distinct on a single column of multi column recordset

[Vachaun22]

I'm having an issue getting distinct records from the database.

What I need is to sum a few columns that are selected based on a range of data and I need it to be distinct on a single column. What happens is, I get a row for each of the values in the range.

Here is the SQL statement I am using:

SELECT F_DETAIL.FD_UNIT, Sum(F_DETAIL.FD_TOT_MILES) AS SumOfFD_TOT_MILES, Sum(F_DETAIL.FD_TOLL_MILES) AS SumOfFD_TOLL_MILES, Sum(F_DETAIL.FD_FUEL) AS SumOfFD_FUEL
FROM F_DETAIL
GROUP BY F_DETAIL.FD_UNIT, F_DETAIL.FD_MONTH, F_DETAIL.FD_YEAR
HAVING (((F_DETAIL.FD_MONTH)>=7 And (F_DETAIL.FD_MONTH)<=9) AND ((F_DETAIL.FD_YEAR)=6))
ORDER BY F_DETAIL.FD_UNIT;

I need the recordset to sum and return a single row for each FD_UNIT number, not each for for FD_MONTH = 7, 8, or 9.

Is there anyway I can return that type of recordset?

 

[nicsin]

try this

SELECT F_DETAIL.FD_UNIT, Sum(F_DETAIL.FD_TOT_MILES) AS SumOfFD_TOT_MILES, Sum(F_DETAIL.FD_TOLL_MILES) AS SumOfFD_TOLL_MILES, Sum(F_DETAIL.FD_FUEL) AS SumOfFD_FUEL
FROM F_DETAIL
HAVING (((F_DETAIL.FD_MONTH)>=7 And (F_DETAIL.FD_MONTH)<=9) AND ((F_DETAIL.FD_YEAR)=6))
GROUP BY F_DETAIL.FD_UNIT
ORDER BY F_DETAIL.FD_UNIT;

N

 

[r937]

SELECT FD_UNIT
     , Sum(FD_TOT_MILES) AS SumOfFD_TOT_MILES
     , Sum(FD_TOLL_MILES) AS SumOfFD_TOLL_MILES
     , Sum(FD_FUEL) AS SumOfFD_FUEL
  FROM F_DETAIL
 WHERE FD_MONTH BETWEEN 7 And 9
   AND FD_YEAR =6
GROUP 
    BY FD_UNIT 
ORDER 
    BY FD_UNIT

r937.com | rudy.ca

 

[lespaul]

you'll need to change the HAVING keyword to WHERE:
[tt]
SELECT F_DETAIL.FD_UNIT, Sum(F_DETAIL.FD_TOT_MILES) AS SumOfFD_TOT_MILES, Sum(F_DETAIL.FD_TOLL_MILES) AS SumOfFD_TOLL_MILES, Sum(F_DETAIL.FD_FUEL) AS SumOfFD_FUEL
FROM F_DETAIL
WHERE (((F_DETAIL.FD_MONTH)>=7 And (F_DETAIL.FD_MONTH)<=9) AND ((F_DETAIL.FD_YEAR)=6))
GROUP BY F_DETAIL.FD_UNIT
ORDER BY F_DETAIL.FD_UNIT;
[/tt]


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Vachaun22]

DOH!!!! That was brainless. Thanks for the help everyone!!!!

 

[nicsin]

sorry change having to where in my post. i suppose you just copied it from access, which tends to write all conditions in the having if there is an aggregate function present

basically just losing the month and year from the grouping will solve your problem

 

[nicsin]

oh sorry guys i didn't see your posts