CODEWITHSUNDEEP
sqlms-access
Ryan_W4588
Ryan_W4588

Reputation: 668

'FIELDNAME' not part of an aggregate function - Access SQL

I am trying to select all [Operation]'s from my database and get data about each one. Below is the SQL code I am trying to use.

The following error occurs:

You tried to execute a query that does not include the specified expression 'op' as part of an aggregate function

My SQL Statement:

SELECT [op] as [Operation], Sum(count) as [Number of Breakdowns], Sum(td) as [Sum of Time Down]
FROM
(
  SELECT [Operation] as op, Count(*) as count, Sum([Time Down]) as td
  FROM tblDailyDowntimeAssy
  WHERE [Type of Maintenance] = 'Breakdown'
UNION ALL
  SELECT [Operation] as op, Count(*) as count, Sum([Time Down]) as td
  FROM tblDailyDowntimeMach
  WHERE [Type of Maintenance] = 'Breakdown'
);

NOTE:

If I try to use just the [Operation] field and don't rename it op, the error still occurs but just changes 'op' to 'operation'

Upvotes: 0

Views: 286

Answers (2)

jaoski
jaoski

Reputation: 1

you need to add a group by clause on your query

select ..... from tbl_name
group by ....

and then add the field to the group by clause mentioned on the error

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

  SELECT [Operation], Sum(count) as [Number of Breakdowns], Sum(td) as [Sum of Time Down]
FROM
(
  SELECT [Operation], Count(*) as count, Sum([Time Down]) as td
  FROM tblDailyDowntimeAssy
  WHERE [Type of Maintenance] = 'Breakdown'
  group by [Operation]
UNION ALL
  SELECT [Operation], Count(*) as count, Sum([Time Down]) as td
  FROM tblDailyDowntimeMach
  WHERE [Type of Maintenance] = 'Breakdown'
  group by [Operation]
)
group by Operation;

You need to add GROUP BY clauses in the queries as you are using aggregate functions.

Upvotes: 2

Related Questions