Just curious on Group By 1

[mr2tho]

I have a view with multiple tables that has a goup by. I am in the process of adding new filters for some of the columns in different tables.

When I add a column to filter, I can click it to "GroupBy" or click on "WHERE" on the Show Criteria pane of the view.
I don't need to see the value.

Is there any difference between two in this situation?

 

[rac2]

Possibly your question should be posted in the Access and Jet SQL forum.

The WHERE clause of a SQL statement is the filter.

The GROUP BY clause gives categories for SUMS and COUNTS, it can also seem like a filter in that it yields one row for each combination of values in the GROUP BY columns.

 

[mr2tho]

No, I am working in SQL 2000. You probably didn't notice I was mentioning about the Show Criteria Pane which is the second from top of the four panes in view in SQL 2k...But in SQL syntax the question would be difference of having after the group by or not having a "having" instead having a where clause before the group by.

 

[rac2]

Sorry about that. Forgive me.

The difference between WHERE and HAVING is this.

WHERE limits the rows which will be COUNTed SUMmed, MAXed, or MINed.

HAVING limits the resulting summary rows.

Think of the GROUP BY query as producing a view or a temporary table based on the underlying tables. The HAVING clause works on this result, it can be based on any item in the SELECT list and nothing else.

The WHERE works on rows in the underlying tables and can use any columns or expression whether not they are in the SELECT list. The WHERE limits the rows which are summarized. The HAVING limits the summaries which are shown.

For example

SELECT state, COUNT(*)
FROM MyMailingList
WHERE zip BETWEEN '10101' AND '90210')
GROUP BY state
HAVING state LIKE 'new%'

will yield numbers for most of the 52 states since most zip codes are counted although a few areas might not be counted.
But the result of the HAVING clase will be to show only the counts for New York, New Mexico, etc.

You cannot simply reverse the role of zip and state. Partly because the HAVING clause may only have items from the SELECT list. More basic however is that the WHERE clause controls (filters) what is counted and the HAVING clause filters what is shown.