Some basic SQL help requested

Factual Questions
1

I need to do a couple of queries that should be pretty simple, but it’s been a long time since I’ve used SQL, and lord knows I wasn’t any good at it even then…

So let’s say that I ran a survey asking people how much they liked some different bands, then dumped the data into SQL. I could end up with a table (actually a view) kinda like this:



Subject      Occupation     Band             AttitudeTowardBand

John         Musician       Beatles          Love them
Paul         Musician       Beatles          Love them
George       Musician       Beatles          Tired of them
Ringo        Musician       Beatles          Love them
Groucho      Actor          Beatles          Hate them
Harpo        Musician       Beatles          Tired of them
Chico        Actor          Beatles          Hate them
Zeppo        Actor          Beatles          Love them
John         Musician       Stones           Hate them
Paul         Musician       Stones           Love them
George       Musician       Stones           Tired of them
Ringo        Musician       Stones           Love them
Groucho      Actor          Stones           Hate them
Harpo        Musician       Stones           Tired of them
Chico        Actor          Stones           Hate them
Zeppo        Actor          Stones           Love them

So I want to be able to run queries that will give me something like the following (for a given band, count the number of responses per attitude regardless of occupation):



Band       Count       Attitude

Beatles    4           Love them
Beatles    2           Tired of them
Beatles    2           Hate them

Or this (for a given band, count the number of responses per attitude and occupation):



Band      Occupation      Count     Attitude

Beatles   Musician        3         Love them
Beatles   Musician        2         Tired of them
Beatles   Actor           1         Love them
Beatles   Actor           2         Hate them

Or this (for a given attitude, count the number of responses per band regardless of occupation):



Attitude       Count     Band

Love them      4         Beatles
Love them      3         Stones

Or this (for a given attitude, count the number of responses per occupation and band):



Attitude      Occupation    Count   Band

Love them     Musician      3       Beatles
Love them     Actor         1       Beatles

Any recommendations on how I could go about doing this?

2

I’m very rusty but I can give you a starting point

SELECT Band, COUNT(*), Attitude FROM table WHERE Band=‘Beatles’ GROUP BY Attitude?

SELECT Band, Occupation, COUNT(*), Attitude FROM table WHERE Band=‘Beatles’ GROUP BY Occupation, Attitude?

SELECT Attitude, COUNT(*), Band FROM table WHERE Attitude=‘Love them’ GROUP BY Band

SELECT Attitude, Occupation, COUNT(*), Band FROM table WHERE Attitude=‘Love them’ GROUP BY Occupation, Band

If I remember my SQL properly(and it has been quite a while since I’ve written any SQL), GROUP BY definitely seems to be what you’re looking for.

3

I do SQL pretty much for a living and it is GROUP BY as given above plus one of the summary functions at the top like count(*). Just saying that to emphasize that is the right advice.

4

When filtering by a grouped field, I think you have to use the HAVING clause instead of WHERE.

5

WHERE is correct for cases like WHERE BAND=‘Beatles’. HAVING lets you access the aggregate functions themselves, so you could say

SELECT Attitude, Band, COUNT()
FROM table GROUP BY Attitude, Band
HAVING COUNT() > 2

Which will show you all attitudes for each band for which 2 or more people selected that choice.

6

No, this isn’t true. You have to use HAVING when filtering on an aggregated field.

You can’t use HAVING to filter on a non-grouped field.

For a grouped field, you could do either, but I think it’s better to use WHERE when you don’t have to.

7

I would caution that it an ANSI SQL requirement that all columns in the SELECT clause that are not part of an aggregate function be included in the GROUP BY clause. For example:

SELECT Col1, Col2, Col3, Count(*)
FROM Employees
GROUP BY Col1, Col2, Col3

is ok, but

SELECT Col1, Col2, Col3, Count(*)
FROM Employees
GROUP BY Col1, Col2

will, depending on what variety of SQL you are using, return either…

  1. an interesting and desireable result
  2. an unexpected and confusing result
  3. an error
8

Got it working. Thanks!