Nested query with distinct function

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

Nested query with distinct function

Author

Topic

EagerSQL
Starting Member

5 Posts

Posted - 2015-01-12 : 01:06:01

I am a newbie. I need to write a simple query but I'm stumped.

I am requesting help for the following query:

I need to know how many widgets are located at each factory.

I have a table called "Widgets". The pertinent column(s) are:
Factory UID

By using only this table I can group the results by the FactoryUID to get the answer. However, this table does not tell me the factory name.

I have a table called "Factories". The pertinent column(s) are:
FactoryUID
FactoryName

I can join these two tables by the FactoryUID. But I don't know how to write this query so that my results will look like the following table:

FactoryName Widgets
Factory1 100
Factory2 200
Factory3 300

I would appreciate your help with this query.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 09:08:07

This sounds like a homework assignment. Post the query you have worked up so far and let's see if we can finish it together.

EagerSQL
Starting Member

5 Posts

Posted - 2015-01-13 : 00:00:17

Unfortunately the queries I've tried are with the db (not at home). This is not a homework assignment. I am trying to help someone out but I have no formal SQL training. I thought if I provided enough information in a forum I would get some help. I don't mind finding the answer on my own. But my head was hurting from banging it on the desk when the queries I've been using weren't working. I just want to tie the factoryname to the factoryid. I've tried using count() and distinct functions but I must be missing a piece because they're returning errors.

EagerSQL
Starting Member

5 Posts

Posted - 2015-01-13 : 01:01:42

Sorry. I don't mean to sound ungrateful. I'm just frustrated. Your help is appreciated.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2015-01-13 : 06:10:02

SELECT f.FactoryName, COUNT(*) WidgetsCnt
FROM Widgets w
JOIN Factories f ON w.FactoryUID = f.FactoryUID
GROUP BY f.FactoryName

--
Chandu

EagerSQL
Starting Member

5 Posts

Posted - 2015-01-13 : 08:11:26

I will try that and let you know. Thank you