getting the count by joining 2 tables

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 2005 Forums

Transact-SQL (2005)

getting the count by joining 2 tables

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-09-18 : 01:05:38

DECLARE @tblDIM TABLE
(
mainCOL int,
effDate datetime
);
INSERT INTO @tblDIM
SELECT 10088, '2008-09-04 23:00:00.000' UNION ALL
SELECT 10152, '2008-09-11 17:00:00.000' UNION ALL
SELECT 10400, '2008-09-05 09:00:00.000' UNION ALL
SELECT 11154, '2008-09-12 07:00:00.000' UNION ALL
SELECT 11420, '2008-09-05 22:00:00.000' UNION ALL
SELECT 11486, '2008-09-13 12:00:00.000' UNION ALL
SELECT 11941, '2008-09-06 08:00:00.000' UNION ALL
SELECT 11949, '2008-09-06 08:00:00.000' UNION ALL
SELECT 11974, '2008-09-12 22:00:00.000' UNION ALL
SELECT 11976, '2008-09-13 11:00:00.000'

DECLARE @tblFACT TABLE
(
mainCOL int,
category int
);
INSERT INTO @tblFACT
SELECT 10088,15 UNION ALL
SELECT 10088,16 UNION ALL
SELECT 10088,17 UNION ALL
SELECT 10152,16 UNION ALL
SELECT 10400,17 UNION ALL
SELECT 10400,18 UNION ALL
SELECT 11154,18 UNION ALL
SELECT 11420,15 UNION ALL
SELECT 11420,18 UNION ALL
SELECT 11486,18 UNION ALL
SELECT 11941,15 UNION ALL
SELECT 11941,16 UNION ALL
SELECT 11949,16 UNION ALL
SELECT 11974,15 UNION ALL
SELECT 11974,16 UNION ALL
SELECT 11974,17 UNION ALL
SELECT 11974,18 UNION ALL
SELECT 11976,15;

--select category,COUNT(mainCOL) AS [mainCOL COUNT] from @tblFACT GROUP BY category

tblFACT table contains multiple entries of mainCOL; hence for considering the count, i take the
last occurrence of mainCOL and its corresponding category
thus for mainCOL - 11974, applicable category will be 18

Expected output:

category mainCOL COUNT
----------- -------------
15 1
16 3
17 1
18 5

i tried using row number, but that doesn't help.
note: i need to do a join because my queries include selecting a date which is available in tblDim and category which is available in tblFact.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 01:50:53

[code]SELECT category,COUNT(*)
FROM
(SELECT t.*
FROM @tblFACT t
JOIN (SELECT mainCOL,MAX(category) as maxcat
FROM @TblFACT
GROUP BY mainCOL) t1
ON t1.mainCOL=t.mainCOL
AND t1.maxcat=t.category)r
GROUP BY category[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 02:00:33

[code]
select category, count(*) AS [mainCOL COUNT]
from
(
select mainCOL, max(category) as category
from @tblFACT
group by mainCOL
) d
group by category
[/code]

KH
[spoiler]Time is always against us[/spoiler]

zion99
Posting Yak Master

141 Posts

Posted - 2008-09-18 : 02:07:17

my fault, tblDIM has to be used because that contains date.

if i need to get the count based on the date - say count required from '2008-09-06' to '2008-09-20'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 02:10:32

[code]
select category, count(*) AS [mainCOL COUNT]
from
(
select f.mainCOL, max(f.category) as category
from @tblFACT f
inner join @tblDIM d on f.mainCOL = d.mainCOL
where d.effDate >= @start_date
and d.effDate < dateadd(day, 1, @end_date)
group by f.mainCOL
) d
group by category
[/code]

KH
[spoiler]Time is always against us[/spoiler]

zion99
Posting Yak Master

141 Posts

Posted - 2008-09-18 : 07:30:54

[code]
DECLARE @tblDIM TABLE
(
mainCOL int,
effDate datetime
);
INSERT INTO @tblDIM
SELECT 10088, '2008-09-04 23:00:00.000' UNION ALL
SELECT 10152, '2008-09-11 17:00:00.000' UNION ALL
SELECT 10400, '2008-09-05 09:00:00.000' UNION ALL
SELECT 11154, '2008-09-12 07:00:00.000' UNION ALL
SELECT 11420, '2008-09-05 22:00:00.000' UNION ALL
SELECT 11486, '2008-09-13 12:00:00.000' UNION ALL
SELECT 11941, '2008-09-06 08:00:00.000' UNION ALL
SELECT 11949, '2008-09-06 08:00:00.000' UNION ALL
SELECT 11974, '2008-09-12 22:00:00.000' UNION ALL
SELECT 11976, '2008-09-13 11:00:00.000'

DECLARE @tblFACT TABLE
(
mainCOL int,
category int,
status int
);
INSERT INTO @tblFACT
SELECT 10088,15,1 UNION ALL
SELECT 10088,17,2 UNION ALL
SELECT 10088,16,6 UNION ALL
SELECT 10152,16,5 UNION ALL
SELECT 10400,18,3 UNION ALL
SELECT 10400,17,4 UNION ALL
SELECT 11154,18,7 UNION ALL
SELECT 11420,15,6 UNION ALL
SELECT 11420,18,2 UNION ALL
SELECT 11486,18,3 UNION ALL
SELECT 11941,15,7 UNION ALL
SELECT 11941,16,1 UNION ALL
SELECT 11949,16,5 UNION ALL
SELECT 11974,15,5 UNION ALL
SELECT 11974,16,7 UNION ALL
SELECT 11974,17,2 UNION ALL
SELECT 11974,18,1 UNION ALL
SELECT 11976,15,3;
[/code]

there is a new column status which decides the latest record among duplicate records.
thus for 11974, latest status is 7 and corresponding category is 16

i tried with this query -
[code]
Select category, count(*) AS [mainCOL COUNT]
from
(
SELECT row_number() over (partition by f.status order by f.status desc) rn
,f.mainCOL, f.category, f.status
from @tblFACT f
inner join @tblDIM d on f.mainCOL = d.mainCOL
where
d.effDate between '2008-09-17' and '2008-09-01'
--and rn = 1
order by f.mainCOL asc, f.status desc
) d where rn = 1
[/code]

I get an error
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." I dont think i can use TOP here.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 09:56:19

Is there anything wrong with the query that I posted ?

quote:


Select category, count(*) AS [mainCOL COUNT]
from
(
	SELECT row_number() over (partition by f.status order by f.status desc) rn 
	,f.mainCOL, f.category, f.status 
	from	@tblFACT f
        inner join @tblDIM d on f.mainCOL = d.mainCOL 
        where   
		d.effDate between '2008-09-17' and '2008-09-01'
		--and rn = 1
		order by f.mainCOL asc, f.status desc
) d where rn = 1

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-18 : 10:43:12

zion99 --

Be sure to read this:

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

(and part II, also)

It should help with understanding how to structure a query with GROUP BY so that you can deal with issues like duplicates and COUNTs and SUMs being off due to joins.

- Jeff
http://weblogs.sqlteam.com/JeffS

zion99
Posting Yak Master

141 Posts

Posted - 2008-09-19 : 01:35:16

quote:
Originally posted by khtan

Is there anything wrong with the query that I posted ?

there is nothing wrong in your query.

. i had a new condition to try for, hence i changed the sample data.

its working now, here's the changed query:


Select category,count(*)-- AS [mainCOL COUNT This week]
from
(
	SELECT row_number() over (partition by f.mainCOL order by f.status desc) rn 
	,f.mainCOL, f.category, f.status 
	from	@tblFACT f
	inner join @tblDIM d on f.mainCOL = d.mainCOL 
	where   d.effDate between '2008-08-17' and '2008-09-21' --@startDate and @endDate
	and category in (15,16,17,18)		
) d where rn = 1 
group by category

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources