SQL expression to remove duplicates from a calculation

Question

I am trying to run a query that will give time averages but when I do... some duplicate records are in the calculation. how can I remove duplicates?

ex.

Column 1 / 07-5794 / 07-5794 / 07-5766 / 07-8423 / 07-4259

Column 2 / 00:59:59 / 00:48:22 / 00:42:48/ 00:51:47 / 00:52:12

I can get the average of the column 2 but I don't want identical values in column 1 to be calculated twice (07-5794) ???

Answer 1

To get the average of the minimum values for each incnum, you could write this SQL

select avg(min_time) as avg_time from
    (select incnum, min(col2) as min_time from inc group by incnum)

using the correct average function for your brand of SQL.

If you're doing this in Access, you'll want to paste this into the SQL view; when you use a subquery, you can't do that directly in design view.

Answer 2

SELECT DISTINCT()

or

GROUP BY ()

or

SELECT UNIQUE()

... but usually averages have duplicates included. Just tell me this isn't for financial software, I wont stand for another abuse of statistics!

Answer 3

I believe you're looking for the DISTINCT.

http://www.sql-tutorial.com/sql-distinct-sql-tutorial && http://www.w3schools.com/SQL/sql_distinct.asp

Answer 4

Do you want to eliminate all entries that are duplicates? That is, should neither of the rows with "07-5794" be included in the calculation. If that is the case, I think this would work (in Oracle at least):

SELECT AVG(col2)
  FROM (
    SELECT col1, MAX(col2) AS col2
      FROM table
      GROUP BY col1
      HAVING COUNT(*) = 1
  )

However, if you want to retain one of the duplicates, you need to specify how to pick which one to keep.

Answer 5

Anastasia,

Assuming you have the calculation for the average and a unique key in the table, you could do something like this to get just the latest occurrence of the timing for each unique 07-xxx result:

select Column1, Avg(Convert(decimal,Column2)) 
from Table1
where TableId in
(
select Max(TableId)
from Table1
group by Column1
)
group by column1

This was assuming the following table structure in MS SQL:

CREATE TABLE [dbo].[Table1] (
[TableId] [int] IDENTITY (1, 1) NOT NULL ,
[Column1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Column2] [int] NULL ) ON [PRIMARY]

Good Luck!

Answer 6

nlucaroni rest assured it's not for financials...i am just a newby at this and do not know how to do this.

Answer 7

so far it sounds like distinct is what i need. i just to figure out how the program will except it .....???? urgh!

Answer 8

Hi could you maybe help a bit more????

I have so far put this expression in... (SELECT distinct (Inc.INCNUM) FROM Inc where IncID = Inc.ID)

Field SQL alias is "Inc.INCNUM" Table sql alias is "Inc"

the problem is that the duplicate stills shows up and the average is still using this line. do i maybe have to put something in that says do use to calculate? thanks