SELECT Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT Statement

Author

Topic

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 12:47:18

I have a table that contains records with a date field, an integer field, and a 'rep' field. Each record has a date attached, but not all reps appear on each date. What I'd like to do is select all dates in the database and the sum of the integer for each date, for one rep code at a time.

example below:

Table:
date integer rep
8/01/02 100 aa
8/02/02 200 bb
8/02/02 100 aa
8/03/02 200 aa
8/04/02 100 bb
8/04/02 100 bb

here are the results i'm trying to get:

date integer rep
8/01/02 0 bb
8/02/02 200 bb
8/03/02 0 bb
8/04/02 200 bb

so this is kind of what i'm trying to use:

SELECT date, sum(integer) FROM table where rep = 'bb'
group by date
order by date

but what i get is:

date integer rep
8/02/02 200 bb
8/04/02 200 bb

including a distinct or a group by clause causes dates with no records to not show up, and using a sub query causes the total sum to show for each date. I know there is a way to do this, but I can't think of it! anyone?

Thanks a bundle to anyone who reades this!

-Teg-

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 13:46:14

-- here abc is the table name
-- a is the date column, b is the rep and c is the integer column

select a.a,a.c,sum(b)
from
(
select a,c
from
(
select distinct a from abc
) A,
(
select distinct c from abc
) B
) A
left outer join abc b
on a.a = b.a and
a.c = b.c
group by a.a,a.c

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 13:47:46

quote:

-- here abc is the table name
-- a is the date column, b is the integer and c is the rep column

select a.a,a.c,sum(b)
from
(
select a,c
from
(
select distinct a from abc
) A,
(
select distinct c from abc
) B
) A
left outer join abc b
on a.a = b.a and
a.c = b.c
group by a.a,a.c

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 14:03:00

this looks really good so far. i'm trying to implement it, and in this line:

on a.a = b.a and

the 'a' and the 'b' are the alias assigned above, correct? A and B?

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 14:07:47

You are right

lozitskiy
Starting Member

28 Posts

Posted - 2002-08-30 : 14:07:58

Try this one:

SELECT m.date
,isnull(select sum(integer)
from table c
where c.date=m.date
and c.rep = 'bb' ),0) as total
,'bb'
FROM table m
group by m.date
order by m.date

-------------
MCP MSSQL

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 14:12:08

lozitskiy, in your reply you use two tables. I only have one table. is one an alias, and if so, which one?

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 14:15:57

skond, in your example it looks like you alias the first table to 'A', the second table to 'B' and then the combined table back to 'A'. I'm getting pretty confused, am I reading this right, and is that what you meant to do?

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 14:16:06

--Here is the query using your names

select a.date,a.rep,isnull(sum(integer),0)
from
(
select date,rep
from
(
select distinct date from table
) A,
(
select distinct rep from table
) B
) A
left outer join table b
on a.date = b.date and
a.rep = b.rep
group by a.date,a.rep
order by 1,2

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 14:21:10

You are right about the way I derived my tables. Anyway I have indented my query to make it more readable.

select a.date,a.rep,isnull(sum(integer),0)
from
(
select date,rep
from
(
select distinct date from table
) A,
(
select distinct rep from table
) B
) A
left outer join table B
on A.date = B.date and
A.rep = B.rep
group by A.date,A.rep
order by 1,2

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 14:22:12

I tried indenting but looks like white spaces are being removed by this site.

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 14:24:53

ok, that gets me all the right information, thank you, but it's not ordering it right. I put the date as the first order by, but it still seems to order it by the second colulmn, which is the rep. any ideas?

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 14:27:51

whoa, scratch that. i didn't have an order by clause in there. when i put it in, it orders by date, so each date repeats for all reps. is there an easy way to order it so each rep has all the dates sequentially?

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 14:31:01

The above query is ordering it by date and within that the rep column. Make sure your datatype for date is datetime. May be it's doing an alphanumeric sort

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 14:33:17

I don't think I can. The date comes in in the format 20020828, so i'd have to change it on the way in. As far as I know SQL Server doesn't convert that format to dates.

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 14:34:16

I could make it an int type tho, would that accomplish the same thing?

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 14:38:14

make the date column mmddyyyy format.

you could use something like
cast(substring(date,5,2)+substring(date,7,2)+left(date,4) as int) in the place of date.

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-30 : 14:43:00

--here is the query

select cast(substring(a.date,5,2)+substring(a.date,7,2)+left(a.date,4) as int),
a.rep,isnull(sum(integer),0)
from
(
select date,rep
from
(
select distinct date from abc
) A,
(
select distinct rep from abc
) B
) A
left outer join abc b
on a.date = b.date and
a.rep = b.rep
group by a.date,a.rep
order by 1,2

tegryan
Starting Member

22 Posts

Posted - 2002-08-30 : 15:24:53

yeah, that does it. Thanks alot skond and lozitskiy, I learned alot today!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources