Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stupid SQL Question:

Stupid SQL Question:

From
shawn everett
Date:
I have a table defined as follows:

CREATE TABLE safety (
    sid SERIAL,
    side INTEGER,
    InjDate DATE,
    InjType INTEGER,
    Outcome INTEGER,
    PRIMARY KEY (sid)
);

What I'm trying to generate is a list of all outcomes and how many of each
that occured for a particular range of dates.

This works but doesn't do dates:

SELECT outcome,count(outcome) from safety
GROUP BY outcome;

How do I incorporte dates into this query.

Adding a WHERE clause to the query doesn't work for example:

SELECT outcome,count(outcome) from safety
GROUP BY outcome
WHERE InjDate='2000-11'14';

Any tips or advice would be apreciated.

Shawn

Re: Stupid SQL Question:

From
"Jonathan Ellis"
Date:
> Adding a WHERE clause to the query doesn't work for example:
>
> SELECT outcome,count(outcome) from safety
> GROUP BY outcome
> WHERE InjDate='2000-11'14';

A WHERE clause must come before a GROUP BY clause.

Also note that if you are using a table with the row defined as datetime
instead of date, you will need something like
WHERE InjDate > '2000-11-14' and InjDate < '2000-11-15'
since unless you specify minutes and seconds, a datetime will assume you
mean 0:00.

-Jonathan

Re: Stupid SQL Question:

From
Michael Fork
Date:
Try these queries...

SELECT injdate, outcome, count(outcome) FROM safety GROUP BY
injdate,outcome;

or

SELECT outcome, count(outcome) FROM safety WHERE injdate='2000-11-14'
GROUP BY outcome;



Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Tue, 14 Nov 2000, shawn everett wrote:

> I have a table defined as follows:
>
> CREATE TABLE safety (
>     sid SERIAL,
>     side INTEGER,
>     InjDate DATE,
>     InjType INTEGER,
>     Outcome INTEGER,
>     PRIMARY KEY (sid)
> );
>
> What I'm trying to generate is a list of all outcomes and how many of each
> that occured for a particular range of dates.
>
> This works but doesn't do dates:
>
> SELECT outcome,count(outcome) from safety
> GROUP BY outcome;
>
> How do I incorporte dates into this query.
>
> Adding a WHERE clause to the query doesn't work for example:
>
> SELECT outcome,count(outcome) from safety
> GROUP BY outcome
> WHERE InjDate='2000-11'14';
>
> Any tips or advice would be apreciated.
>
> Shawn
>

Re: Stupid SQL Question:

From
shawn everett
Date:
Putting the WHERE clause before the GROUP BY clause worked.  I'm too
stupid or too tired to think clearly. :)

Thanks all

Shawn

On Tue, 14 Nov 2000, Michael Fork wrote:

> Try these queries...
>
> SELECT injdate, outcome, count(outcome) FROM safety GROUP BY
> injdate,outcome;
>
> or
>
> SELECT outcome, count(outcome) FROM safety WHERE injdate='2000-11-14'
> GROUP BY outcome;
>
>
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Tue, 14 Nov 2000, shawn everett wrote:
>
> > I have a table defined as follows:
> >
> > CREATE TABLE safety (
> >     sid SERIAL,
> >     side INTEGER,
> >     InjDate DATE,
> >     InjType INTEGER,
> >     Outcome INTEGER,
> >     PRIMARY KEY (sid)
> > );
> >
> > What I'm trying to generate is a list of all outcomes and how many of each
> > that occured for a particular range of dates.
> >
> > This works but doesn't do dates:
> >
> > SELECT outcome,count(outcome) from safety
> > GROUP BY outcome;
> >
> > How do I incorporte dates into this query.
> >
> > Adding a WHERE clause to the query doesn't work for example:
> >
> > SELECT outcome,count(outcome) from safety
> > GROUP BY outcome
> > WHERE InjDate='2000-11'14';
> >
> > Any tips or advice would be apreciated.
> >
> > Shawn
> >
>