SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Thread: Count 2 selects

  1. #1
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count 2 selects

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

    Code:
    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
    monkey - the rest is history

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    monkey - the rest is history

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?:

    Code:
    SELECT	Status, COUNT(Status) AS PriorityStatusCount
    FROM	Priorities
    GROUP BY Status
    Thanks for your help
    monkey - the rest is history

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    inner joins can be more efficient than left outer joins when there are no actual outer cases

    for your other question, use COALESCE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    inner joins can be more efficient than left outer joins when there are no actual outer cases

    for your other question, use COALESCE
    Thanks Where do I use it - i tried:

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

    cheers again
    monkey - the rest is history

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's the correct way to use COALESCE...

    presumably this did not give you what you wanted?

    you'll have to explain more, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by boxhead View Post
    Thanks Where do I use it - i tried:

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

    cheers again
    Code:
    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
    monkey - the rest is history

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    monkey - the rest is history

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by boxhead View Post
    it should be Priorities.Status
    in that case you'll need a left table which has all statuses

    but that's easy to conjure up
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    monkey - the rest is history

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by boxhead View Post
    any ideas?
    not until i understand what you're doing with those variables, and that will require that i understand your table relationships

    start 'splainin, lucy...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    not until i understand what you're doing with those variables, and that will require that i understand your table relationships

    start 'splainin, lucy...
    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
    monkey - the rest is history

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) it did - now it doesn't
    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
    monkey - the rest is history

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by boxhead View Post
    1) it did - now it doesn't
    just for my curiosity, could you show the view's source code please


    Quote Originally Posted by boxhead View Post
    How do I get my c# code to decide which AND statements are used in a SQL procedure?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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
    Quote Originally Posted by r937 View Post
    @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
    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
    monkey - the rest is history


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •