COUNT, SUM and GROUP BY Question

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)

COUNT, SUM and GROUP BY Question

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-06-12 : 10:29:51

Hi

I have this query:
SELECT Locations.LocationPointer AS Location, COUNT(Containers.containerid) AS TotalContainers, Locations.MaxCap AS MaximumCapacity
FROM Locations INNER JOIN
Containers ON Locations.LocationID = Containers.LocationID
GROUP BY Locations.LocationPointer, Locations.MaxCap

Here's the explanation:
Two tables --> CONTAINERS and LOCATIONS.

A LOCATION, let's say 001, can hold a certain amount of containers. They're linked on location.locationid = containers.locationid.

so when I execute the above query, I get a list of locations in use and next to it the total number of containers currently in those locations.

Now I want to add another field called TOTAL FILES. That will be the total number of records returned from indexdata where indexdata inner join on containers with indexdata.containerid = containers.containerid.

For instance, say I have location 001.
In location 001 there are 3 containers, a,b,c.
Inside container a there's 3 records indexed in indexdata.
Inside container b there's 2 records indexed in indexdata.
In container c there's 0 records.
Containers are linked to Indexdata with containerid.
How would I add the contents of container a and b up and display it like this:

LOCATION TOTAL CONTAINERS TOTAL FILES AV FILES / CONTAINER
001 3 5 1.6

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-12 : 10:51:11

maybe someone else can make sense of your requirements, but I cannot. Can you please provide create table statements and sample data? the more specific info you can provide, and if you can provide any code to get us started, the better and easier we can help you out and there's a lot less guessing involved.

- Jeff
http://weblogs.sqlteam.com/JeffS

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-12 : 11:01:43

Maybe something like:

SELECT L.LocationPointer AS Location
    ,L.MaxCap AS MaximumCapacity
    ,C.TotalContainers
    ,D.TotalFiles
    ,1.0 * D.TotalFiles/C.TotalContainers AS AvgFilesInContainer
FROM Locations L
    JOIN (
        SELECT C1.LocationID, COUNT(*) AS TotalContainers
        FROM Containers C1
        GROUP BY C1.LocationID
    ) C
    ON L.LocationID = C.LocationID
    JOIN (
        SELECT C2.LocationID, COUNT(*) AS TotalFiles
        FROM Containers C2
            JOIN IndexData I2
                ON C2.ContainerID = I2.ContainerID
        GROUP BY C2.LocationID
    ) F
    ON L.LocationID = F.LocationID

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-06-12 : 11:03:14

This SQL statement:

SELECT Locations.LocationPointer AS Location, COUNT(Containers.containerid) AS TotalContainers, Locations.MaxCap AS MaximumCapacity
FROM Locations INNER JOIN
Containers ON Locations.LocationID = Containers.LocationID
GROUP BY Locations.LocationPointer, Locations.MaxCap

Gives me a list of locations and how many containers are currently linked to those locations. See a location as a category, and inside a category there will be a couple of containers. They are joined by locations.locationid = containers.containerid.

So the above statement gives me a list of locations in the database and next to it the total number of containers inside that location, as joined by the containers.locationid = locations.locationid.

So that's easy.

Then I have another table with indexdata. Indexdata is always joined with containers on containers.containerid = indexdata.containerid. So I could say "select * from indexdata, containers where indexdata.containerid = containers.containerid.".

That would give me a list of indexdata records joined with a particular container. Now I want to say - give me a list of locations, then next to each location, give me the total number of containers linked to it, and then next to each container total, I want to know how many files in total is in that location.

In my laymen's terms it would be like this:

tables look like this:

[LOCATIONS]
locationid (int auto increment)
locationpointer (string)
maxcap (int)

[CONTAINERS]
containerid (int auto inc)
containercode (str)
containerdesc (str)
locationid (int)

[INDEXDATA]
indexid (int auto inc)
containerid (int)
field1... field2... (all str)

I can join containers with locations and get the list of locations and total number of containers next to them. But now I also want to get the total number of files in all containers in a given location.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources