count - 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)

count

Author

Topic

tup
Starting Member

13 Posts

Posted - 2003-08-01 : 07:32:49

I have a table something like this:

dega tip nr
---- ---- -------
0002 30 10090843
0003 30 00015882
0000 30 01008918
0001 30 10007607
0001 30 10079628
0001 30 10122085
0004 232 10033684
0006 30 08079659

I want to count the rows from nr field where tip<49 grouped by dega,
and also to count rows from nr field where tip >199 grouped by dega.

It would have look something like this

dega nr
----- ----
0002 xxxx ( where tip <49)
0002 xxxx ( where tip >199)

... and so on

thanks to everyone who is willing to help

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-01 : 07:39:06

will this work, or do you want seperate lines for each count:


select dega,
 sum(case when tip < 49 then 1 else 0 end) as LessThan49,
 sum(case when tip > 199 then 1 else 0 end) as MoreThan199
from
 table
group by dega

- Jeff

tup
Starting Member

13 Posts

Posted - 2003-08-01 : 08:44:31

[quote]Originally posted by jsmith8858

will this work, or do you want seperate lines for each count:

[code]
select dega,
sum(case when tip < 49 then 1 else 0 end) as LessThan49,
sum(case when tip > 199 then 1 else 0 end) as MoreThan199
from
table
group by dega

It works all right but I would like to do it with seperate lines for each count .

Thanks a lot .

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-01 : 09:12:34

Try this

SELECT dega
     , CASE WHEN tip < 49 THEN 'LessThan49'
          WHEN tip > 199 THEN 'MoreThan199'
          ELSE null END as 'ReportInd'
     , COUNT(nr) 'NR_Count'
FROM table
GROUP BY dega
     , CASE WHEN tip < 49 THEN 'LessThan49'
          WHEN tip > 199 THEN 'MoreThan199'
          ELSE null END

-- if you need to exclude all other tip conditions this might work

HAVING (CASE WHEN tip < 49 THEN 'LessThan49'
          WHEN tip > 199 THEN 'MoreThan199'
          ELSE null END) is not null

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-01 : 09:18:28

you will need to create a field that tells you which count you are returning. here's one way to do it:

select dega, count(*) as Number, 'Tip<49' as Condition
from
dega
where tip < 49
group by dega
union all
select dega, count(*) as Number, 'Tip>199' as condition
from
dega
where Tip> 199
group by dega

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources