count group col1/col2 AND col2/col3? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

count group col1/col2 AND col2/col3?

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-02 : 14:15:45

I have 3 fields I want to count and group based on same moves from pick to load and load to delto
so if the load to delto is the same as the pick to load group it together and count. see below

table
pick | load | delto
move1  move2  move3
move2  move3  move2
move1  move2  move1
move1  move3  move2
move2  move3  move2

results

qty | from | to
2     move1  move2
3     move2  move3
3     move3  move2
1     move2  move1
1     move1  move3

thanks

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-02 : 14:26:27

[code]
CREATE TABLE #temp
(
pick VARCHAR(10),
[LOAD] VARCHAR(10),
delto VARCHAR(10)
)
insert into #temp
select 'move1', 'move2', 'move3'
union all select 'move2', 'move3', 'move2'
union all select 'move1', 'move2', 'move1'
union all select 'move1', 'move3', 'move2'
union all select 'move2', 'move3', 'move2'

SELECT sum(qty) AS qty , A.[from], A.[to]
FROM (
SELECT COUNT(*) AS qty, [LOAD] AS [From], delto AS [to]
FROM #temp GROUP BY [load],delto
union
SELECT COUNT(*) AS qty, pick AS [from], [load] AS [to]
FROM #temp GROUP BY pick, [load]
) AS A
GROUP BY A.[From],A.[to]

[/code]

An infinite universe is the ultimate cartesian product.

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-02 : 14:43:56

great thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources