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!
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
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
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
in that case you’ll need a left table which has all statuses
but that’s easy to conjure up
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?
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 ;))
if the view contains only inner joins, you’re okay using it, otherwise all bets are off
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
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?
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.