Some basic SQL help needed - SQL Server Forums

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

Some basic SQL help needed

Author

Topic

AxeSlash
Starting Member

5 Posts

Posted - 2011-02-03 : 10:55:40

Warning: I'm a complete newbie, go gentle on me!

OK so I have a couple of tables:

Items
-----
ID
ContainerID
ItemTypeID

ItemTypes
---------
ID
Description

I want to return a query that looks like this:

ItemsInThisContainer
--------------------
Qty
ItemTypeDescription

Basically items can be assigned to a container (so each Item gets a ContainerID)
I want to generate a list of what items (ItemType.Description) are in that container, with quantities.

So e.g.

Item 0001: ContainerID=C0001, ItemTypeID=IT0001
Item 0002: ContainerID=C0001, ItemTypeID=IT0001
Item 0003: ContainerID=C0001, ItemTypeID=IT0002
Item 0004: ContainerID=C0002, ItemTypeID=IT0001

So if I wanted to get the list of stuff in ContainerID C0001, I would get:

IT0001's Description | 2
IT0002's Description | 1

I've been going around in circles trying to figure out the SQL to do this, can anyone shed any light?

Do I need more than one line of SQL?
Do I need to do the counting bit in the front end instead of in the SQL?

Thanks in advance

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-03 : 11:40:18

quote:

IT0001's Description | 2
IT0002's Description | 1

Can you please explain the logic of
IT0001's Description | 2
and not
IT0001's Description | 3

is this:

Select ItemTypeID,sum(Quantity)
From
(
Select ItemTypeId, 1 as Quantity
From yourtable
Group by ItemTypeId,ContainerID
)
Group by ItemTypeID

AxeSlash
Starting Member

5 Posts

Posted - 2011-02-04 : 07:54:12

quote:
Originally posted by pk_bohra
Can you please explain the logic of
IT0001's Description | 2
and not
IT0001's Description | 3

Because there are only two IT0001s in C0001. The other one is in C0002 (so I don't want to return that).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources