Count Help - 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)

Count Help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-27 : 10:22:55

[code]
I need a query which should output the result based on the dt:

DECLARE @MT TABLE

(

TNO Int,

TID Int,
TID1 Int,

Dt datetime

)

INSERT INTO @MT (TNO, TID,TID1,dt)

(
Select '256' , '1','1','5-4-2007'
Union All
Select '257', '','1','5-4-2007'
Union All
Select '258', '','1','6-4-2007'
Union All
Select '259', '1','','7-4-2007'
Union All
Select '259', '1','','7-4-2007'
Union All
Select '259', '1','','7-4-2007'
)

DECLARE @HIFRAP1 TABLE

(

TID Int,

DSCRPTR VARCHAR(50)

)

INSERT INTO @HIFRAP1 (TID, DSCRPTR)

(
Select '1' , 'BOOK3'

)

DECLARE @HIFRAP2 TABLE

(

TID Int,

DSCRPTR VARCHAR(50)

)

INSERT INTO @HIFRAP2 (TID, DSCRPTR)

(
Select '1' , 'BOOK2'

)
Desired Output

Desc Count
----- -----
BOOK2 3
BOOK3 2
Morethan1Book 1

COndition:If there is data in TID1,TID2.
I want to make the count 1 in morethan1Book.
else if there is data in TID1 or TID2 then i want count to the respective book2 or book3
Please let me know if m not clear.

[/code]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 11:43:38

It is not clear from the data how you arrived at the totals. for Book2, you are doing a count(*) from @MT so the count is 3 (excluding 1st row because it has values for both TID and TID1). For Book3, how did you get a 2?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-27 : 12:06:05

[code]

Table @MT:
----------

TNO TID TID1 Dt
--- ---- ---- -----------------------
256 1 1 2007-05-04 00:00:00.000
257 0 1 2007-05-04 00:00:00.000
258 0 1 2007-06-04 00:00:00.000
259 1 0 2007-07-04 00:00:00.000
259 1 0 2007-07-04 00:00:00.000
259 1 0 2007-07-04 00:00:00.000

Book2 =count(Tid)
Book3= count(TID1) ( Book3 count is 2 since 1st row has both the values for TID and TID1)

[/code]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 12:12:26

If you look at your code:

quote:

Originally posted by sqldba2k6


DECLARE @HIFRAP1 TABLE 

( 

TID Int, 

DSCRPTR VARCHAR(50) 

) 



INSERT INTO @HIFRAP1 (TID, DSCRPTR) 

( 
Select '1' , 'BOOK3' 

) 


DECLARE @HIFRAP2 TABLE 

( 

TID Int, 

DSCRPTR VARCHAR(50) 

) 



INSERT INTO @HIFRAP2 (TID, DSCRPTR) 

( 
Select '1' , 'BOOK2' 

)

You used TID for both the tables. So it appears TID is the FK from @MT to these 2 tables.

If book3= count(TID1) then your code should be:


DECLARE @HIFRAP1 TABLE ( TID1 Int, DSCRPTR VARCHAR(50))

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 12:23:05

[code]
select Descr=H1.DSCRPTR , count(TNo)
from @HIFRAP1 H1
join @MT M ON H1.TID1 = M.TID1 And (M.TID <> M.TID1 )
Group by H1.DSCRPTR
UNION
select Descr=H2.DSCRPTR , count(TNo)
from @HIFRAP2 H2
join @MT M ON H2.TID = M.TID And (M.TID <> M.TID1 )
Group by H2.DSCRPTR
UNION
Select Descr ='Morethan1Book', count(*)
FROM @MT M2
WHERE M2.TID = M2.TID1
Group By TNO
[/code]

The last SELECT for more than one book is based on the assumption that there will be no records in @MT which do not map to any of the @H1 or @H2 records.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 15:40:43

Dinakar, there is a more simple solution

And no change to column names are necessary!

-- Prepare sample data
DECLARE @MT TABLE (TNO INT, TID INT, TID1 INT, Dt DATETIME) 

INSERT	@MT
SELECT	'256' , '1', '1', '5-4-2007' UNION ALL 
SELECT	'257', '', '1', '5-4-2007' UNION ALL 
SELECT	'258', '', '1', '6-4-2007' UNION ALL 
SELECT	'259', '1', '', '7-4-2007' UNION ALL 
SELECT	'259', '1', '', '7-4-2007' UNION ALL 
SELECT	'259', '1', '', '7-4-2007' 

DECLARE @HIFRAP1 TABLE (TID INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP1
SELECT	'1' , 'BOOK3'

DECLARE @HIFRAP2 TABLE (TID INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP2
SELECT	'1', 'BOOK2' 

-- Show the expected output
SELECT		[Desc],
		COUNT(*) AS [Count]
FROM		(
			SELECT		CASE
						WHEN h1.DSCRPTR IS NULL THEN h2.DSCRPTR
						WHEN h2.DSCRPTR IS NULL THEN h1.DSCRPTR
						ELSE 'More than 1 book'
					END AS [Desc]
			FROM		@MT AS mt
			LEFT JOIN	@HIFRAP1 h1 ON h1.TID = mt.TID
			LEFT JOIN	@HIFRAP2 h2 ON h2.TID = mt.TID1
		) AS d
GROUP BY	d.[Desc]
ORDER BY	d.[Desc]

E 12°55'05.25"
N 56°04'39.16"

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-08-07 : 16:45:49


DECLARE @MT TABLE (TNO INT, TID1 INT,TID2 INT,TID3 INT,TID4 INT,TID5 INT,TID6 INT,TID7 int, Dt DATETIME) 
INSERT	@MT
SELECT	'256' , '1', '1','1','1','1','1','1','5-4-2007' UNION ALL 
SELECT	'257', '1', '','','','','','', '5-4-2007' UNION ALL 
SELECT	'258','', '1','','','','','', '6-4-2007' UNION ALL 
SELECT	'259', '1', '1','1','','','','', '7-4-2007' UNION ALL 
SELECT	'259', '', '','','','1','','','4-4-2007' UNION ALL 
SELECT	'260', '', '','1','','','','','7-4-2007' UNION ALL 
SELECT	'261','1', '','','','','','','5-4-2007' UNION ALL 
SELECT	'262', '1', '','','','','','1','6-4-2007' UNION ALL 
SELECT	'263', '', '','','1','1','1','1','7-4-2007' 

select * from @MT

DECLARE @HIFRAP1 TABLE (TID1 INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP1
SELECT	'1' , 'BOOK1'

DECLARE @HIFRAP2 TABLE (TID2 INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP2
SELECT	'1', 'BOOK2' 


DECLARE @HIFRAP3 TABLE (TID3 INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP3
SELECT	'1', 'BOOK3'


DECLARE @HIFRAP4 TABLE (TID4 INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP4
SELECT	'1', 'BOOK4'


DECLARE @HIFRAP5 TABLE (TID5 INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP5
SELECT	'1', 'BOOK5'

DECLARE @HIFRAP6 TABLE (TID6 INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP6
SELECT	'1', 'BOOK6'

DECLARE @HIFRAP7 TABLE (TID7 INT, DSCRPTR VARCHAR(50)) 

INSERT	@HIFRAP7
SELECT	'1', 'BOOK7'




I am getting the correct Output if i not passing the date in where condition:

Book count
---- -----  
Morethan1 4

I am not getting desired output Morethan1 with the where condition dt=

below is my code:

select Descr ='More than 1 Book', count(*) as Count
FROM Table M2
WHERE M2.TID1 in( M2.TID2,M2.TID3,M2.TID4,M2.TID5,M2.TID6,M2.TID7)
or M2.TID2 in( M2.TID1,M2.TID3,M2.TID4,M2.TID5,M2.TID6,M2.TID7)
or M2.TID3 in( M2.TID2,M2.TID1,M2.TID4,M2.TID5,M2.TID6,M2.TID7)
or M2.TID4 in( M2.TID2,M2.TID3,M2.TID1,M2.TID5,M2.TID6,M2.TID7)
or M2.TID5 in( M2.TID2,M2.TID3,M2.TID4,M2.TID1,M2.TID6,M2.TID7)
or M2.TID6 in( M2.TID2,M2.TID3,M2.TID4,M2.TID5,M2.TID1,M2.TID7)
or M2.TID7 in( M2.TID2,M2.TID3,M2.TID4,M2.TID5,M2.TID6,M2.TID1)
where (dt IS NULL OR dt Between dt And dt)

please help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources