query help, hard for me, I hope easy for you

query help, hard for me, I hope easy for you

Post by Bert » Tue, 14 Nov 2000 04:00:00



I have a table called tblTmpEvent.  This table contains 3 columns

1. eventkey(primary key of table)
2.  event code (a number 1-100 identifying what type of event it was)
3.  bdiscards (has a value of true or false)

I want to create a query that shows results  as follows

1. the event code (1-100)
2. a count of how many events that had the corresponding event code and a
bdiscards value=1

I figured out how to write two separate queries but have not figured out how
to get them to work together

select Event_Code, Count(Event_Code) as Events

from tblTmpEvent

group by Event_Code

order by Event_Code

this groups the eventcode but does nothing with bdiscards

select Event_Code, count(bDiscard) as Discards

from tblTmpEvent

where bDiscard = 1

group by Event_Code

order by Event_Code

this groups the bdiscards but does not group the event

Any help would be appreciated

Bert

 
 
 

query help, hard for me, I hope easy for you

Post by Joe Celk » Tue, 14 Nov 2000 04:00:00


Quote:>> I have a table called tblTmpEvent.  This table contains 3 columns ..

<<

Two  things: why not post DDL instead of narrative?  It is a lot easier
to use when someone wants to tes the code.  Next, dtop using those
silly 1950's Fortran BASIC prefixes; SQL compilers ar smart enough to
know that the PHYSICAL storage for the (logical) model uses tables; do
you put "n-" in front of your nouns when you write?  Also, consider
using plural or collective nouns for table names, since they are
usually sets, classses or collections of things and not a single item.

Myt guess at your table is this:

CREATE TABLE TmpEvents
(event_id CHAR(15) NOT NULL PRIMARY KEY,
 event_code INTEGER NOT NULL
            CHEWCK (event_code BETWEEN 1 AND 100),
 bdiscards INTEGER NOT NULLO
            CHECK (bdiscards IN (0,1)));

Quote:>> I want to create a query that shows results  as follows

 1. the event code (1-100)
 2. a count of how many events that had the corresponding event code
where bdiscards = 1 <<

If you are using zero and one as the values for bdiscards, then this
will do the job:

SELECT event_code,
       COUNT(event_code) AS events,
       SUM(bdiscards) AS discards
  FROM TmpEvents
 GROUP BY event_code;

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

query help, hard for me, I hope easy for you

Post by Bert » Tue, 14 Nov 2000 04:00:00


I really appreciate the help.....The only problem is that this is a database
I inherited ( hence the tblevents)  The field bdiscards is a Boolean
true\false value not 1 or 0, so when I run the query you gave me the sum
part does not work correctly.


Quote:

> >> I have a table called tblTmpEvent.  This table contains 3 columns ..
> <<

> Two  things: why not post DDL instead of narrative?  It is a lot easier
> to use when someone wants to tes the code.  Next, dtop using those
> silly 1950's Fortran BASIC prefixes; SQL compilers ar smart enough to
> know that the PHYSICAL storage for the (logical) model uses tables; do
> you put "n-" in front of your nouns when you write?  Also, consider
> using plural or collective nouns for table names, since they are
> usually sets, classses or collections of things and not a single item.

> Myt guess at your table is this:

> CREATE TABLE TmpEvents
> (event_id CHAR(15) NOT NULL PRIMARY KEY,
>  event_code INTEGER NOT NULL
>             CHEWCK (event_code BETWEEN 1 AND 100),
>  bdiscards INTEGER NOT NULLO
>             CHECK (bdiscards IN (0,1)));

> >> I want to create a query that shows results  as follows

>  1. the event code (1-100)
>  2. a count of how many events that had the corresponding event code
> where bdiscards = 1 <<

> If you are using zero and one as the values for bdiscards, then this
> will do the job:

> SELECT event_code,
>        COUNT(event_code) AS events,
>        SUM(bdiscards) AS discards
>   FROM TmpEvents
>  GROUP BY event_code;

> --CELKO--
> Joe Celko, SQL Guru & DBA at Trilogy
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> which can be cut and pasted into Query Analyzer is appreciated.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

query help, hard for me, I hope easy for you

Post by BP Margoli » Tue, 14 Nov 2000 04:00:00


Bert,

SQL Server does not support a Boolean data type. SQL Server does support a
Bit data type (0, 1, NULL). My guess is that you have an Access database.
Might I suggest that it would be useful in future posts to identify the
database platform you are using.

I'm not sure whether this will work in Access ... this is after all a SQL
Server newsgroup ... but you might try ...

SELECT event_code,
       COUNT(event_code) AS events,
       MIN(bdiscards) AS discards
  FROM TmpEvents
 GROUP BY event_code;

If I remember my Access correctly, Access identifies a TRUE as a -1 and a
FALSE as a 0, so I believe that MIN should work ... if it doesn't, try
substituting a MAX.

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> I really appreciate the help.....The only problem is that this is a
database
> I inherited ( hence the tblevents)  The field bdiscards is a Boolean
> true\false value not 1 or 0, so when I run the query you gave me the sum
> part does not work correctly.



> > >> I have a table called tblTmpEvent.  This table contains 3 columns ..
> > <<

> > Two  things: why not post DDL instead of narrative?  It is a lot easier
> > to use when someone wants to tes the code.  Next, dtop using those
> > silly 1950's Fortran BASIC prefixes; SQL compilers ar smart enough to
> > know that the PHYSICAL storage for the (logical) model uses tables; do
> > you put "n-" in front of your nouns when you write?  Also, consider
> > using plural or collective nouns for table names, since they are
> > usually sets, classses or collections of things and not a single item.

> > Myt guess at your table is this:

> > CREATE TABLE TmpEvents
> > (event_id CHAR(15) NOT NULL PRIMARY KEY,
> >  event_code INTEGER NOT NULL
> >             CHEWCK (event_code BETWEEN 1 AND 100),
> >  bdiscards INTEGER NOT NULLO
> >             CHECK (bdiscards IN (0,1)));

> > >> I want to create a query that shows results  as follows

> >  1. the event code (1-100)
> >  2. a count of how many events that had the corresponding event code
> > where bdiscards = 1 <<

> > If you are using zero and one as the values for bdiscards, then this
> > will do the job:

> > SELECT event_code,
> >        COUNT(event_code) AS events,
> >        SUM(bdiscards) AS discards
> >   FROM TmpEvents
> >  GROUP BY event_code;

> > --CELKO--
> > Joe Celko, SQL Guru & DBA at Trilogy
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> > which can be cut and pasted into Query Analyzer is appreciated.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

 
 
 

query help, hard for me, I hope easy for you

Post by Bert » Fri, 17 Nov 2000 04:00:00


I am actually using a SQL 2000 database.  But for some reason when I run the
query I get negative values.  Any idea why?>
BErt

> Bert,

> SQL Server does not support a Boolean data type. SQL Server does support a
> Bit data type (0, 1, NULL). My guess is that you have an Access database.
> Might I suggest that it would be useful in future posts to identify the
> database platform you are using.

> I'm not sure whether this will work in Access ... this is after all a SQL
> Server newsgroup ... but you might try ...

> SELECT event_code,
>        COUNT(event_code) AS events,
>        MIN(bdiscards) AS discards
>   FROM TmpEvents
>  GROUP BY event_code;

> If I remember my Access correctly, Access identifies a TRUE as a -1 and a
> FALSE as a 0, so I believe that MIN should work ... if it doesn't, try
> substituting a MAX.

> ----------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I really appreciate the help.....The only problem is that this is a
> database
> > I inherited ( hence the tblevents)  The field bdiscards is a Boolean
> > true\false value not 1 or 0, so when I run the query you gave me the sum
> > part does not work correctly.



> > > >> I have a table called tblTmpEvent.  This table contains 3 columns
..
> > > <<

> > > Two  things: why not post DDL instead of narrative?  It is a lot
easier
> > > to use when someone wants to tes the code.  Next, dtop using those
> > > silly 1950's Fortran BASIC prefixes; SQL compilers ar smart enough to
> > > know that the PHYSICAL storage for the (logical) model uses tables; do
> > > you put "n-" in front of your nouns when you write?  Also, consider
> > > using plural or collective nouns for table names, since they are
> > > usually sets, classses or collections of things and not a single item.

> > > Myt guess at your table is this:

> > > CREATE TABLE TmpEvents
> > > (event_id CHAR(15) NOT NULL PRIMARY KEY,
> > >  event_code INTEGER NOT NULL
> > >             CHEWCK (event_code BETWEEN 1 AND 100),
> > >  bdiscards INTEGER NOT NULLO
> > >             CHECK (bdiscards IN (0,1)));

> > > >> I want to create a query that shows results  as follows

> > >  1. the event code (1-100)
> > >  2. a count of how many events that had the corresponding event code
> > > where bdiscards = 1 <<

> > > If you are using zero and one as the values for bdiscards, then this
> > > will do the job:

> > > SELECT event_code,
> > >        COUNT(event_code) AS events,
> > >        SUM(bdiscards) AS discards
> > >   FROM TmpEvents
> > >  GROUP BY event_code;

> > > --CELKO--
> > > Joe Celko, SQL Guru & DBA at Trilogy
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> > > which can be cut and pasted into Query Analyzer is appreciated.

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

 
 
 

query help, hard for me, I hope easy for you

Post by BP Margoli » Fri, 17 Nov 2000 04:00:00


Bert,

I have to admit to confusion ...

You post that you are using SQL Server 2000, but earlier you posted: "The
field bdiscards is a Boolean true\false value not 1 or 0".

As I replied, SQL Server does not support a Boolean data type ... so it is
not clear exactly what the data type of bdiscards is ... could you offer
some clarification as to exactly what the data type of bdiscards is and some
of the sample data in the column.

Thanks.

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> I am actually using a SQL 2000 database.  But for some reason when I run
the
> query I get negative values.  Any idea why?>
> BErt


> > Bert,

> > SQL Server does not support a Boolean data type. SQL Server does support
a
> > Bit data type (0, 1, NULL). My guess is that you have an Access
database.
> > Might I suggest that it would be useful in future posts to identify the
> > database platform you are using.

> > I'm not sure whether this will work in Access ... this is after all a
SQL
> > Server newsgroup ... but you might try ...

> > SELECT event_code,
> >        COUNT(event_code) AS events,
> >        MIN(bdiscards) AS discards
> >   FROM TmpEvents
> >  GROUP BY event_code;

> > If I remember my Access correctly, Access identifies a TRUE as a -1 and
a
> > FALSE as a 0, so I believe that MIN should work ... if it doesn't, try
> > substituting a MAX.

> > ----------------------------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I really appreciate the help.....The only problem is that this is a
> > database
> > > I inherited ( hence the tblevents)  The field bdiscards is a Boolean
> > > true\false value not 1 or 0, so when I run the query you gave me the
sum
> > > part does not work correctly.



> > > > >> I have a table called tblTmpEvent.  This table contains 3 columns
> ..
> > > > <<

> > > > Two  things: why not post DDL instead of narrative?  It is a lot
> easier
> > > > to use when someone wants to tes the code.  Next, dtop using those
> > > > silly 1950's Fortran BASIC prefixes; SQL compilers ar smart enough
to
> > > > know that the PHYSICAL storage for the (logical) model uses tables;
do
> > > > you put "n-" in front of your nouns when you write?  Also, consider
> > > > using plural or collective nouns for table names, since they are
> > > > usually sets, classses or collections of things and not a single
item.

> > > > Myt guess at your table is this:

> > > > CREATE TABLE TmpEvents
> > > > (event_id CHAR(15) NOT NULL PRIMARY KEY,
> > > >  event_code INTEGER NOT NULL
> > > >             CHEWCK (event_code BETWEEN 1 AND 100),
> > > >  bdiscards INTEGER NOT NULLO
> > > >             CHECK (bdiscards IN (0,1)));

> > > > >> I want to create a query that shows results  as follows

> > > >  1. the event code (1-100)
> > > >  2. a count of how many events that had the corresponding event code
> > > > where bdiscards = 1 <<

> > > > If you are using zero and one as the values for bdiscards, then this
> > > > will do the job:

> > > > SELECT event_code,
> > > >        COUNT(event_code) AS events,
> > > >        SUM(bdiscards) AS discards
> > > >   FROM TmpEvents
> > > >  GROUP BY event_code;

> > > > --CELKO--
> > > > Joe Celko, SQL Guru & DBA at Trilogy
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> > > > which can be cut and pasted into Query Analyzer is appreciated.

> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.

 
 
 

1. Help with my(temporary,I hope) mental block(easy one)

Try

Select max(a.date),
       a.IDNumber,
       a.dept
    from tablename a
   group by IDNumber, dept;

(Let the qep & the server do the work)

Dafydd Hughes
Felixstowe Dock & Railway Co.

2. multiserver jobs

3. Inserting/Updating/Deleting On Paradox Tables

4. SQL - not too hard I hope

5. Restore corrupt Master.DAT

6. Easy ? .....I hope

7. Business Partnering

8. odbc call failed (Easy I hope)

9. Need help for a simple query (I hope :)

10. Easy one I hope

11. Moving Transaction Log (easy question, I hope)

12. Easy question ( I hope! )