Re: Query Conflict

uBob Quintal <rquintal@xxxxxxxxxxxxx> wrote in
news:Xns99BA5EA86A7ECBQuintal@xxxxxxxxxxxxx:

In a summary query, any field in the WHERE clause also has to be
in the SELECT clause. because the where is applied after the
sum(), average() etc... and if it's in the SELECT clause it has to
be in a function like sum(), first(), etc... or in the GROUP BY
Clause

To get around this, there is a HAVING clause. which is applicable
to the records before the sum() is performed.Just change AND AND
(([Lake Worth... to HAVING AND (([Lake Worth C... and it should
work.

This is wrong.

However, I'll admit, it was something *I* didn't know until the last
cople of years. Here is some perfectly valoid SQL:

SELECT MyTable.Created, Count(MyTable.InventoryID) AS Count
FROM
MyTable
WHERE MyTable.UpdatedBy="MWB"
GROUP BY MyTable.Created;

You only need HAVING when you want to have criteria on the results
of the summary.

Until recently, I didn't know there was a WHERE option in the
dropdown list (where GroupBy and Sum and so forth are) in the QBE --
it's at the bottom of the list, and I never scrolled down there to
see it. Because of that, I'd been doing complex subqueries to get my
base recordset filtered so I could apply my summaries to the right
records, since if it's in the group by you end up with the wrong
numbers.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages

  • Re: newbie problem with analytic function
    ... I think order by clause dont cause for cummulative sum or running ... for the purpose of calculationg the partition results. ... sumover (partition by deptno order by empno) tot_dept_sal ...
    (comp.databases.oracle.misc)
  • Re: Summary query sum from different tables
    ... automatically changes it to field: Cost, Table: prior month, and Total: Sum ... I have tried using teh SQL view to add HAVING clause: ...
    (microsoft.public.access.queries)
  • Re: Hide zeros in totals of crosstab query
    ... You need to put the criteria for the Sum in a HAVING clause. ... clause applies in selecting the records to aggregate; ...
    (microsoft.public.access.queries)
  • Re: newbie-SQL Count
    ... group function is not allowed here ... How can I count each Column that has a POSITIVE number and put the sum ... quantity column of the same row or sum them for the entire table? ... I think posting the full where clause would be helpful to ...
    (comp.databases.oracle.server)
  • Re: newbie problem with analytic function
    ... for the purpose of calculationg the partition results. ... cumulative sum of the salary within a department. ... example where no order by clause exists within the partition by clause ... You should always include the Oracle edition and version in a post ...
    (comp.databases.oracle.misc)