# Some basic SQL help requested
**URL:** https://boards.straightdope.com/t/some-basic-sql-help-requested/410152
**Category:** Factual Questions
**Created:** [July 1, 2007, 8:04pm UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152 "2007-07-01T20:04:59Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@Hunter\_Hawk](https://boards.straightdope.com/u/Hunter_Hawk)
#### Post date: [July 1, 2007, 8:04pm UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/1 "2007-07-01T20:04:59Z")
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:
```auto
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):
```auto
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):
```auto
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):
```auto
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):
```auto
Attitude Occupation Count Band
Love them Musician 3 Beatles
Love them Actor 1 Beatles
```
Any recommendations on how I could go about doing this?
---
### Author:  [@Rysto](https://boards.straightdope.com/u/Rysto)
#### Post date: [July 1, 2007, 8:43pm UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/2 "2007-07-01T20:43:31Z")
I’m very rusty but I can give you a starting point
[QUOTE=Hunter Hawk]
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):
[/quote]
SELECT Band, COUNT(\*), Attitude FROM table WHERE Band=‘Beatles’ GROUP BY Attitude?
> [@](#):
>
> Or this (for a given band, count the number of responses per attitude and occupation):
SELECT Band, Occupation, COUNT(\*), Attitude FROM table WHERE Band=‘Beatles’ GROUP BY Occupation, Attitude?
> [@](#):
>
> Or this (for a given attitude, count the number of responses per band regardless of occupation):
SELECT Attitude, COUNT(\*), Band FROM table WHERE Attitude=‘Love them’ GROUP BY Band
> [@](#):
>
> Or this (for a given attitude, count the number of responses per occupation and band):
SELECT Attitude, Occupation, COUNT(\*), Band FROM table WHERE Attitude=‘Love them’ GROUP BY Occupation, Band
> [@](#):
>
> Any recommendations on how I could go about doing this?
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.
---
### Author:  [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)
#### Post date: [July 1, 2007, 9:51pm UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/3 "2007-07-01T21:51:09Z")
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.
---
### Author:  [@EllisDee](https://boards.straightdope.com/u/EllisDee)
#### Post date: [July 2, 2007, 12:45am UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/4 "2007-07-02T00:45:39Z")
When filtering by a grouped field, I think you have to use the HAVING clause instead of WHERE.
---
### Author:  [@tanstaafl](https://boards.straightdope.com/u/tanstaafl)
#### Post date: [July 2, 2007, 1:52am UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/5 "2007-07-02T01:52:02Z")
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.
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [July 2, 2007, 2:25am UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/6 "2007-07-02T02:25:50Z")
[QUOTE=Ellis Dee]
When filtering by a grouped field, I think you have to use the HAVING clause instead of WHERE.
[/QUOTE]
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.
---
### Author:  [@K364](https://boards.straightdope.com/u/K364)
#### Post date: [July 2, 2007, 2:29am UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/7 "2007-07-02T02:29:03Z")
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
---
### Author:  [@Hunter\_Hawk](https://boards.straightdope.com/u/Hunter_Hawk)
#### Post date: [July 3, 2007, 3:50am UTC](https://boards.straightdope.com/t/some-basic-sql-help-requested/410152/8 "2007-07-03T03:50:16Z")
Got it working. Thanks!