Count 2 selects

I have the following two select statements which each return a figure:


SELECT	Activities.Activity, COUNT(*) AS Meetings
FROM	Activities LEFT OUTER JOIN
        Sector ON Activities.AreaID = Sector.ID LEFT OUTER JOIN
        Division ON Sector.DivID = Division.ID
WHERE	Activities.Activity = 1 AND Activities.AreaLevel = 'Town'
GROUP BY Activities.Activity

SELECT	Activities.Activity, COUNT(*) AS Meetings
FROM	Activities LEFT OUTER JOIN
	Town ON Activities.AreaID = Town.ID LEFT OUTER JOIN		
	Sector ON Town.SectorID = Sector.ID LEFT OUTER JOIN
        Division ON Sector.DivID = Division.ID
WHERE	Activities.Activity = 1 AND Activities.AreaLevel = 'Sector'
GROUP BY Activities.Activity

What I what is to join these so it gives me one total figure rather than 2 seperate. I have tried putting one inside the other’s FROM Clause but got in a bit of a mess! Any help greatly appreciated!

Cheers

monkey

i notice you are using LEFT OUTER JOINs, but also using COUNT(*) instead of COUNT(Division.ID) to count

could you please verify that the counts you are getting are accurate

if they are, it means that there aren’t any divisions that aren’t related, so you should be uninf INNER JOINs

The counts I am getting are acurate. Do you mean there are no Divisions which don’t relate to a Sector? If so , then this is correct (my sql skills are not very advanced to be honest!)

cheers

**Changed it now to use inner joins and Activities.ID and all still works fine :slight_smile:

SELECT ( SELECT COUNT(*) 
           FROM Activities 
         INNER
           JOIN Sector 
             ON Sector.ID = Activities.AreaID
         INNER
           JOIN Division 
             ON Division.ID = Sector.DivID
          WHERE Activities.Activity = 1 
            AND Activities.AreaLevel = 'Town' ) +
       ( SELECT COUNT(*)
           FROM Activities 
         INNER
           JOIN Town 
             ON Town.ID = Activities.AreaID
         INNER 
           JOIN Sector 
             ON Sector.ID = Town.SectorID
         INNER
           JOIN Division 
             ON Division.ID = Sector.DivID
          WHERE Activities.Activity = 1 
            AND Activities.AreaLevel = 'Sector' ) AS total

A lot simpler than i thought - easy when you know how! What is the benefit of using the inner joins over left out join - they gave the same results?

Cheers

Can you answer this one as well - how do I get the following to return a single row count of 0 if no rows are found?:


SELECT	Status, COUNT(Status) AS PriorityStatusCount
FROM	Priorities
GROUP BY Status

Thanks for your help :slight_smile:

inner joins can be more efficient than left outer joins when there are no actual outer cases

for your other question, use COALESCE :slight_smile:

Thanks :slight_smile: Where do I use it - i tried:

SELECT Status, COALESCE(COUNT(Status), 0) AS PriorityStatusCount

cheers again :slight_smile:

that’s the correct way to use COALESCE…

presumably this did not give you what you wanted?

you’ll have to explain more, eh

:slight_smile:


SELECT	Status, COALESCE(COUNT(Status), 0) AS PriorityStatusCount
FROM	Priorities INNER JOIN
	vStructure ON Priorities.TownID = vStructure.TownID
WHERE	vStructure.TownID = ISNULL(@TownID, vStructure.TownID) AND
	vStructure.SectorID = ISNULL(@SectorID, vStructure.SectorID) AND
	vStructure.DivID = ISNULL(@DivID, vStructure.DivID) AND
	Status IN (1,2,3,4)
GROUP BY Status

This should return a count of each of the 4 status types (where each is present) or 1 row with 0

cheers

which table is the Status column in? i’m guessing vStructure

thus, use a LEFT OUTER JOIN instead (and fix your WHERE clause to be part of the ON clause)

My bad - it should be Priorities.Status

cheers

in that case you’ll need a left table which has all statuses

but that’s easy to conjure up :wink:

SELECT conjure.Status
     , COALESCE(COUNT(Priorities.Status),0) AS PriorityStatusCount
  FROM ( SELECT 1 AS Status
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4 ) AS conjure
LEFT OUTER
  JOIN Priorities 
    ON Priorities.Status = conjure.Status
LEFT OUTER 
  JOIN vStructure 
    ON vStructure.TownID = Priorities.TownID
   AND vStructure.TownID = ISNULL(@TownID, vStructure.TownID) 
   AND vStructure.SectorID = ISNULL(@SectorID, vStructure.SectorID) 
   AND vStructure.DivID = ISNULL(@DivID, vStructure.DivID)
GROUP 
    BY conjure.Status

Thanks but that doesn’t quite work! I get what I expect and am now getting 0 values, butnot matter what variables I plug in a alway get the full count not filtered - any ideas?

cheers

not until i understand what you’re doing with those variables, and that will require that i understand your table relationships

start 'splainin, lucy… :wink:

OK, i’ll start with the basics and go from there!

Every Priority is linked to a town, a town linked to a sector and a sector to a division (vStructure is a view joining the town, sector and division info together to save me having to keep doing it :))

I want to be able to able to find a count of each priorities.status for a given area - ie for a town, all the towns in a sector or all the towns within a division.

Does that help (and my name’s not Lucy…actually, you’ve been so helpful you can call me what you want ;))

cheers

i have two immediate suggestions

  1. if the view contains only inner joins, you’re okay using it, otherwise all bets are off

  2. obviously the @TownID and other values are being set by your application language, so you should use your application language to decide which AND condition to include in your query, so leave the ISNULL stuff out

  1. it did - now it doesn’t :wink:
  2. Not sure what you mean. As I understand it, my c# code calls the procedure and passes the variable the procedure requires. If I don’t want to use the variable I still need to pass a NULL value. How do I get my c# code to decide which AND statements are used in a SQL procedure?

cheers

just for my curiosity, could you show the view’s source code please

pseudo-code –

@sqlstring = ‘SELECT … ’
IF @TownID IS NOT NULL THEN @sqlstring += ’ AND townid = @TownID
IF @SectorID IS NOT NULL THEN @sqlstring += ’ AND sectorid = @SectorID
IF @DivID IS NOT NULL THEN @sqlstring += ’ AND divid = @DivID
EXECUTE @sqlstring


SELECT Div.Name AS DivName, Sector.Name AS SectorName, Town.Name AS TownName, Div.ID AS Div, Sector.ID AS SectorID, Town.ID AS TownID
FROM Town INNER JOIN
         Sector ON Town.Sector_ID = Sector.ID INNER JOIN
         Div ON Sector.Div_ID = Div.ID

I thought that might be what you meant. We try to avoid adding sql code to our applications where ever possible, preferring to use procedures. This is to allow sql people to work with all neccessary code independantly of app code.

cheers