count for various conditions in a single query from same tab

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 for various conditions in a single query from same tab

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-13 : 08:53:29

Ravjeet writes "Hi,
I am working on a query where I need to fetch records from the same table for various where conditions. The size of the database is very large.
The table structure is like
Table One



ID	Descr    Category_Id   Type_id    Date  
1 	 abc 	  2	        1	 1/1/2006
2 	 sf    	  1 	        2	 2/1/2006
3 	 ddfd	  5 	        6	 2/1/2006
4 	 dg   	  1 	        3	 3/1/2006
5 	 fdg  	  2 	        1	 4/1/2006
6 	 dfg  	  4 	        5	 4/1/2006

The query I want is something like
- find number of counts group by Category_id and Type_id
- find total number of records where date is greater than 2/1/2006(Some date)
- find total number of records where date is less than 3/1/2006(some date)
- find total number of records
etc in a single query

If I do
Select Category_id ,Type_id
,count(*) as total,(Select count(*) from tableone where date > '2/1/2006' and categoryid ='smehing' and type_id = somethign) as total2,(Select count(*) from tableone where date < '3/1/2006' and categoryid ='smehing' and type_id = somethign) as total3 from tableone
group by Category_id ,Type_id


categoryid      Typeid        total         total1         total2
   1              2            1              0            
   1              3            1              1 
   2              1            2              1
   4              5            1              0
   5              6            1              1

then it displays the result but takes a lot of time in fetching it. can u provide me with a better solution

Thanks in advance"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-13 : 08:55:56

Read about Cross-tab Reports in sql server help file
Also refer
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-13 : 09:01:16

Not sure why you are getting different values for total1, as your code suggests a fixed value, but I assume you meant to use a correlated query.

Dunno if it will be any faster, but you could try:


Select T1.Category_id,
       T1.Type_id,
       count(*) as total,
       [total2],
       [total3]
from tableone
     LEFT OUTER JOIN
     (
         Select categoryid, type_id, count(*) as total2
         from   tableone 
         where date > '2/1/2006' 
         GROUP BY categoryid, type_id
     ) AS T2
         ON T2.categoryid = T1.categoryid
        AND T2.type_id= T1.type_id
     LEFT OUTER JOIN
     (
         Select categoryid, type_id, count(*) as total3
         from   tableone 
         where date < '3/1/2006' 
         GROUP BY categoryid, type_id
     ) AS T3
         ON T3.categoryid = T1.categoryid
        AND T3.type_id= T1.type_id
group by Category_id ,Type_id

Kristen

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-13 : 10:49:21

Hi all,

Ravjeet - here's an example one of the kinds of things you can do...

--data
declare @t table (ID int, Descr varchar(10), Category_Id int, Type_id int, Date datetime)
set dateformat mdy
insert @t
          select 1,  'abc', 2, 1, '1/1/2006'
union all select 2,   'sf', 1, 2, '2/1/2006'
union all select 3, 'ddfd', 5, 6, '2/1/2006'
union all select 4,   'dg', 1, 3, '3/1/2006'
union all select 5,  'fdg', 2, 1, '4/1/2006'
union all select 6,  'dfg', 4, 5, '4/1/2006'

--calculation
select Category_Id, Type_id,
    count(*) as total1,
    sum(case when Date > '2/1/2006' then 1 else 0 end) as total2,
    sum(case when Date < '3/1/2006' then 1 else 0 end) as total3
from @t
group by Category_Id, Type_id
order by Category_Id, Type_id

/*results
Category_Id Type_id     total1      total2      total3      
----------- ----------- ----------- ----------- ----------- 
1           2           1           0           1
1           3           1           1           0
2           1           2           1           1
4           5           1           1           0
5           6           1           0           1
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources