SQL query - 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

SQL Server Development (2000)

SQL query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-02 : 08:28:53

Sunitha writes "Hi,

I have created the following sql query:

select sizegrp,reactcode, count(*)as cnt1 from mis_group_query
where period = '200406' and reactcode in('00','04','05')
group by reactcode, sizegrp space(40)
order by reactcode, sizegrp

1) and would like to know if I want to create a line space between the different reactcode when they are displace how would I do this?

The output at the moment is:

sizegrp reactcode cnp1
1 00 1
2 00 6
3 00 11
4 00 30
4 01 1
1 03 1
2 03 1
1 04 3
2 04 29
3 04 41
4 04 227

2) if I want to use add all the grouped cnt1's how would I do this?"

dsdeming

479 Posts

Posted - 2004-12-02 : 14:43:42

See ROLLUP Operator in BOL for the answer to your second question. As to your first question, I don't know of any good way to do it.

Just thinking out loud here... It could be done by inserting the results into a temp table with an IDENTITY( 1, 2 ) column in it, turning IDENTITY INSERT on, and then inserting a row with emtpy strings and an identity value between the existing identity value for the last row with a reactcode and the first row with the next reactcode.

Dennis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-12-03 : 06:58:00

For (2) try this


Select sum(cnt1) from(
select sizegrp,reactcode, count(*)as cnt1 from mis_group_query
where period = '200406' and reactcode in('00','04','05')
group by reactcode, sizegrp space(40)
order by reactcode, sizegrp
) as t

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources