Grouping and Counting with Same Table

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)

Grouping and Counting with Same Table

Author

Topic

suchiate
Starting Member

33 Posts

Posted - 2007-10-26 : 00:13:50

Hi Guys,

Currently I have a BatchID table, linked to the details table, whereby storing all the items in the particular batch. Some items have been checked out and some have not. Batches are bind to a particular User so right now, I would like to use one single query to call out the total checked out and total unchecked out items from these three tables. But I get weird values when I combine the two of them in one single query. Please advice.

Following is a sample of my script

Select UserName, Count(I.ItemID) as Checked, Count(I2.ItemID) as Unchecked
From tblBatch B INNER JOIN tblUser U ON B.Batchfor = U.UserID
INNER JOIN tblItem I ON B.BatchID = I.BatchID AND I.CheckedOut = 1
INNER JOIN tblItem I2 ON B.BatchID = I2.BatchID AND I2.CheckedOut = 0
Group By UserName

Hope you guys can help me find out where my problem is.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-26 : 00:17:28

Try

Select UserName, Count(I.ItemID) as Checked, Count(I2.ItemID) as Unchecked
From tblBatch B INNER JOIN tblUser U ON B.Batchfor = U.UserID
LEFT JOIN tblItem I ON B.BatchID = I.BatchID AND I.CheckedOut = 1
LEFT JOIN tblItem I2 ON B.BatchID = I2.BatchID AND I2.CheckedOut = 0
Group By UserName

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

suchiate
Starting Member

33 Posts

Posted - 2007-10-26 : 02:25:05

thank you for your reply khtan,

but it doesnt work, let say the result supposed to be

Checked out
item 1 - 1
item 2 - 5
item 3 - 4

Unchecked
item 1 - 19
item 2 - 95
item 3 - 296

But the result shows the values

Checked out
item 1 - 19
item 2 - 475
item 3 - 392

Unchecked
item 1 - 19
item 2 - 475
item 3 - 492

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 02:42:13

khtan unfortenately wrote INNER JOINs that behave like CROSS JOIN due to multiple Fks.
Look at this example


DECLARE	@a TABLE (pk INT)
DECLARE	@b TABLE (fk INT, i INT)
DECLARE	@c TABLE (fk INT, j INT)

INSERT	@a
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3

INSERT	@b
SELECT	1, 1 UNION ALL
SELECT	1, 3 UNION ALL
SELECT	2, 4 UNION ALL
SELECT	2, 8 UNION ALL
SELECT	2, 10 UNION ALL
SELECT	3, 1

INSERT	@c
SELECT	1, 11 UNION ALL
SELECT	1, 13 UNION ALL
SELECT	2, 14 UNION ALL
SELECT	2, 18 UNION ALL
SELECT	2, 60 UNION ALL
SELECT	3, 11

select		a.pk,
		SUM(b.i) AS SumAct,
		SUM(c.j) AS SumDebt
from		@a as a
inner join	@b as b on b.fk = a.pk
inner join	@c as c on c.fk = a.pk
group by	a.pk

I think sum 276 is to high! Should only be 92.
The reason for this is that there also is 3 records in @b table that share same fk. 92 times 3 is 276.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 02:50:10

[code]SELECT u.UserName,
SUM(CASE WHEN i.CheckedOut = 1 THEN 1 ELSE 0 END) AS Checked,
SUM(CASE WHEN i.CheckedOut = 0 THEN 1 ELSE 0 END) AS Unchecked
FROM tblItem AS i
INNER JOIN tblBatch AS b ON b.BatchID = i.BatchID
INNER JOIN tblUser AS u ON u.UserID = b.BatchFor
GROUP BY u.UserName
ORDER BY u.UserName[/code]

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

suchiate
Starting Member

33 Posts

Posted - 2007-10-26 : 04:16:04

Thank you so much Peso,

your solution works perfectly.

=)

And also thanks for your explanation with the example on how it actually works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources