[thelist] SQL: using GROUP BY and COUNT

[Chris W. Parker]

> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
> Sent: Monday, August 19, 2002 12:33 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL: using GROUP BY and COUNT
>
>
> > why do you 'AS "keyword"' if you never reference "keyword" again?
> > does it serve another purpose?
>
> provides convenient column alias

but you never reference the alias 'keyword' again so why specify it? i
was asking if there was another purpose behind it.

> > why COUNT(*)? does that mean 'COUNT how many records are
> returned' or
> >does that mean to 'COUNT the number of GROUP'd records'? for
> example...
>
> count(*), like other aggregate functions, always refers to a group
>
> the tricky part is, when GROUP BY is omitted, then the entire
> result set is
> a (single) group
>
> for example,
>
>  select max(dateofbirth)
>    from employees
>
> will treat the entire employees table as one group and give
> you the max()
> for that whole group

i'm still confused by this. maybe i should ask, why COUNT(*) and not
COUNT(zrSearchString)?

> what was the zrFromWhere column for?  referer ip number?

that is where on the site the search was performed. "home page",
"patrol", stuff like that. although it's not totally necessary it's got
to be helpful in some way to see where the popular search areas are and
what people are clicking on the most.


> what's the name of the column the search string has to be found in?

i'm not sure what you mean. if by search string you mean
('larry','curly','moe'), then the column those terms need to be found in
is zrSearchString.

maybe i'm misunderstanding.

> i've called it foo
>
> SELECT zrSearchString, zrFromWhere, count(*)
> FROM ZeroResults
> WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02#
> AND  foo LIKE '*' & zrSearchString & '*'
> AND zrSearchString   IN ('larry','curly',moe')
> GROUP BY zrSearchString, zrFromWhere
> ORDER BY COUNT(*) DESC, zrSearchString
>
> substitute the list of keywords to analyze in place of the
> three stooges
>
> notice zrSearchString has asterisks concatenated around it, those are
> msaccess wildcards, i assumed msaccess because of its weird
> date strings

yes access. that's why i said "(vbscript/access)" in my first email. ;)



chris.

p.s.

> p.s. your column names reminded me of
>
> "What you are doing is trying to expose the physical storage
> choices in
> your logical data model all over again. And you are making
> your code hard
> to read. Try to read "Paris in the Spring," "nounParis prepIn artThe
> nounSpring" and see if the prefixes make it easier to understand; now
> imagine that was a 20 word sentence with subclauses."
>     -- Ten Things I Hate About You by Joe Celko.
> http://www.intelligententerprise.com/001205/celko1_1.shtml

i'm not prefixing the data type of the column, but rather the table that
column is in (which you may already know.) zr = zero results. i
understand the comparison though. but not putting prefixes makes me feel
all weird inside. so i put prefixes.