Want to double count in a single query

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

General SQL Server Forums

New to SQL Server Programming

Want to double count in a single query

Author

Topic

KWMKWM
Starting Member

3 Posts

Posted - 2010-11-05 : 15:34:31

I want to double count in a SQLSERVER 2008 query, here
is my sample example.


TableA
  field itemid
  field itemgroupid

TableB
  field Bomid

I want the query to count using a where
clause on Table A


   select count(itemid) from TableA
   where itemgroupid in
  ('190','250','252','253','255')),

and then give me a count of how many
Bomid's in TableB are attached to all
the results from the select/where on
TableA.

The relationship of Table A to Table B
is one-to-many, Table A has unique entrys
while table B can have many connections to
TableA, the key is itemid.

So the result I am looking for would
look like this:

TableAresult TableBResult
60,000 180,000

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-05 : 16:07:16

[code]

SELECT * FROM
(SELECT COUNT(*) AS TABLEA_COUNT FROM TABLEA) AS A
CROSS JOIN
(SELECT COUNT(*) AS TABLEB_COUNT FROM TABLEB) AS B

[/code]
????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-05 : 16:27:21

DECLARE
@t TABLE
(
itmID int NOT NULL
)
;

INSERT INTO @t
(
itmID
)
SELECT
itemid
FROM
TableA
WHERE
(itemgroupid IN ('190','250','252','253','255'))
;

SELECT
(
SELECT
COUNT(itmID)
FROM
@t
) AS ACount
,(
SELECT
COUNT(b.Bomid)
FROM
TableB AS b
INNER JOIN @t AS t
ON b.itemID = t.itmID
) AS BCount
;

I think that works....

KWMKWM
Starting Member

3 Posts

Posted - 2010-11-05 : 16:32:26

Hi, it worked as far as joining the 2 counts but there was no logic to it, I only
want the query in TableB to count the resultset of TableA.

So if there was 100 records in TableA and only 50 of them met the
" where itemgroupid in ('190','250','252','253','255'))," criteria, AND 1000 records in Tbale B that only 500 matched the itemid in Table A, then I would like to see
a count of 50 followed by a count of 500, not a toal count of 100 and 1000.
Kevin

KWMKWM
Starting Member

3 Posts

Posted - 2010-11-05 : 16:46:51

Hi TimSMan,
I changed the int to a char and it all worked, thanks!

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-05 : 16:53:45

Sweet. Welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources