query help 12-05-09

i am using sql server 2000 and i need to do the following.  sql server
2005 made stuff like this easy.

i have a table like this (pseudo coded)

CREATE TABLE CONTRACT (CONTRACT_ID INT, CONTRACT_NUMBER VARCHAR(10),
CONTRACT_REGION VARCHAR(10), STATUS VARCHAR(20), CONTRACT_DATE
DATETIME)

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (1, '1000', 'EAST', 'IN PROGRESS', '2007-01-01')

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (2, '2000', 'EAST', 'IN PROGRESS', '2007-01-01')

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (3, '3000', 'EAST', 'COMPLETE', '2007-01-01')

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (3, '3000', 'EAST', 'IN PROGRESS', '2008-01-01')

I need to get a list of of of this data with a total count of
CONTRACT_ID by CONTRACT_NUMBER and CONTRACT_REGION

example output i'm looking for

CONTRACT_ID   CONTRACT_NUMBER CONTRACT_REGION   STATUS
TOTAL_CONTRACT_ID_COUNT
1                       1000
EAST                           IN PROGRESS     1
2                       2000
EAST                           IN PROGRESS     1
3                       3000
EAST                          COMPLETE           2
4                       3000
EAST                           IN PROGRESS     2


i can't seem to get the query right.  can anyone help me out?
0
Derek
12/5/2009 12:00:21 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
689 Views

Similar Articles

[PageSpeed] 41

I think what you want is

Select c.CONTRACT_ID, c.CONTRACT_NUMBER, c.CONTRACT_REGION, c.STATUS,
  (Select Count(*) From CONTRACT c2 Where c.CONTRACT_NUMBER = 
c2.CONTRACT_NUMBER And c.CONTRACT_NUMBER = c2.CONTRACT_NUMBER) As 
CONTRACT_ID_COUNT
From CONTRACT c;

Tom

"Derek" <gepetto_2000@yahoo.com> wrote in message 
news:71031f81-76d7-430e-a46f-ba4f2aa45ab7@m16g2000yqc.googlegroups.com...
>i am using sql server 2000 and i need to do the following.  sql server
> 2005 made stuff like this easy.
>
> i have a table like this (pseudo coded)
>
> CREATE TABLE CONTRACT (CONTRACT_ID INT, CONTRACT_NUMBER VARCHAR(10),
> CONTRACT_REGION VARCHAR(10), STATUS VARCHAR(20), CONTRACT_DATE
> DATETIME)
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (1, '1000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (2, '2000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (3, '3000', 'EAST', 'COMPLETE', '2007-01-01')
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (3, '3000', 'EAST', 'IN PROGRESS', '2008-01-01')
>
> I need to get a list of of of this data with a total count of
> CONTRACT_ID by CONTRACT_NUMBER and CONTRACT_REGION
>
> example output i'm looking for
>
> CONTRACT_ID   CONTRACT_NUMBER CONTRACT_REGION   STATUS
> TOTAL_CONTRACT_ID_COUNT
> 1                       1000
> EAST                           IN PROGRESS     1
> 2                       2000
> EAST                           IN PROGRESS     1
> 3                       3000
> EAST                          COMPLETE           2
> 4                       3000
> EAST                           IN PROGRESS     2
>
>
> i can't seem to get the query right.  can anyone help me out? 

0
Tom
12/5/2009 12:20:51 AM
thanks!!!!!

On Dec 4, 7:20=A0pm, "Tom Cooper" <tomcoo...@comcast.net> wrote:
> I think what you want is
>
> Select c.CONTRACT_ID, c.CONTRACT_NUMBER, c.CONTRACT_REGION, c.STATUS,
> =A0 (Select Count(*) From CONTRACT c2 Where c.CONTRACT_NUMBER =3D
> c2.CONTRACT_NUMBER And c.CONTRACT_NUMBER =3D c2.CONTRACT_NUMBER) As
> CONTRACT_ID_COUNT
> From CONTRACT c;
>
> Tom
>
> "Derek" <gepetto_2...@yahoo.com> wrote in message
>
> news:71031f81-76d7-430e-a46f-ba4f2aa45ab7@m16g2000yqc.googlegroups.com...
>
> >i am using sql server 2000 and i need to do the following. =A0sql server
> > 2005 made stuff like this easy.
>
> > i have a table like this (pseudo coded)
>
> > CREATE TABLE CONTRACT (CONTRACT_ID INT, CONTRACT_NUMBER VARCHAR(10),
> > CONTRACT_REGION VARCHAR(10), STATUS VARCHAR(20), CONTRACT_DATE
> > DATETIME)
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (1, '1000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, =A0CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (2, '2000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, =A0CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (3, '3000', 'EAST', 'COMPLETE', '2007-01-01')
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, =A0CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (3, '3000', 'EAST', 'IN PROGRESS', '2008-01-01')
>
> > I need to get a list of of of this data with a total count of
> > CONTRACT_ID by CONTRACT_NUMBER and CONTRACT_REGION
>
> > example output i'm looking for
>
> > CONTRACT_ID =A0 CONTRACT_NUMBER CONTRACT_REGION =A0 STATUS
> > TOTAL_CONTRACT_ID_COUNT
> > 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 IN PROGRESS =
=A0 =A0 1
> > 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 2000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 IN PROGRESS =
=A0 =A0 1
> > 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0COMPLETE =A0 =
=A0 =A0 =A0 =A0 2
> > 4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 IN PROGRESS =
=A0 =A0 2
>
> > i can't seem to get the query right. =A0can anyone help me out?

0
Derek
12/5/2009 12:46:42 AM
Reply:

Similar Artilces: