Help With Select Count Query (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 2000 Forums

SQL Server Development (2000)

Help With Select Count Query (2 TABLES)

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2008-05-29 : 17:39:57

I have code where I count Quantity - Now I have been tasked to count VIA the Table that holds the sescriptions (table 1) and count the Occurancies from Table 2 (Which is my table that holds the actual inventory).
My original Query is "SELECT SETTYPE,COUNT(*)AS record_count FROM TABLE2 GROUP BY SETTYPE Order by SETTYPE"

Where should I start?
Query TABLE 1 - (SELECT SET_TYPE,DESCR,COST_CENTER FROM TABLE1 WHERE COST_CENTER = '19') NOW I would like to get the information from the Original Query into this from TABLE 2. Just a little lost on getting this job done correctly.

thank you

nhaas
Yak Posting Veteran

90 Posts

Posted - 2008-05-29 : 19:18:06

ok I think I did it, is there a better way than this?

SELECT TABLE2.SETTYPE, COUNT(*) as record_count FROM TABLE2,TABLE1 WHERE TABLE2.SETTYPE=TABLE1.SET_TYPE and TABLE1.COST_CENTER = '19' GROUP BY SETTYPE ORDER BY SETTYPE

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 19:26:15

It's very hard to follow what you want as your description isn't very clear. It is always best to not only provide a description but to also provide sample data that illustrates your problem. That way if there is a language barrier, your issue is complex, or perhaps you aren't the best describer and are too close to your environment unlike us, it will be easier for us to figure out what you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

nhaas
Yak Posting Veteran

90 Posts

Posted - 2008-05-29 : 19:40:09

Thanks I will Try to Explain what a want.
I have two Tables, one has a Description of Devices that I have (Description to model number), The Other has an Inventory - say all of the telephones that I have with model numbers.

Table 1:
SETTYPE,DESCription, CLASS
XY1,Blue Phone, 10
XY2,RED Phone Phone, 11

TABLE 2:
SETTYPE, PhoneNumber, UserINFO
XY1,123, Jane
XY1,126, BOB
XY2,128, MIKE

I want to LOOP through TABLE2 and find out the number of Devices from Table 1 that I have.. so from the example above 2 -XY1 and 1 XY2

(ALSO In the reverse I want to find out if there is any errors for Billing - if a device listed in TABLE2 is not in TABLE one show me! freeform data base with no error checking turned on..........)

hopefully that helps a little more.

Thank you for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 19:47:58

1.

SELECT SETTYPE, COUNT(*)
FROM Table2 t2
INNER JOIN Table1 t1
ON t2.SETTYPE = t1.SETTYPE
GROUP BY t2.SETTYPE

2. You should have a foreign key constraint between Table1.SETTYPE and Table2.SETTYPE so that you'll never have this "error" condition. But here you go anyway:

SELECT SETTYPE
FROM Table1 t1
WHERE NOT EXISTS (SELECT * FROM Table2 t2 WHERE t1.SETTYPE = t2.SETTYPE)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

nhaas
Yak Posting Veteran

90 Posts

Posted - 2008-05-30 : 11:28:11

Thank you - looks much better and faster than what I had...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources