Results 1 to 8 of 8

Thread: Query Assistance
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    1,180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Query Assistance

    I have the following Query...

    Code:
    SELECT [Location], Count(X) AS CountOfX
    FROM TempQuery
    WHERE [Region]="Madagascar" AND X="Y"
    GROUP BY [Location]
    I want a 3rd column which indicates the count when X='N', but I'm drawing a blank.

    Any assistance would be greatly appreciated.
    If the world didn't suck, we'd all fall off.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query Assistance

    Something like this maybe?

    Code:
    SELECT [Location], X, COUNT(X) AS CountOfX
    FROM TempQuery
    WHERE [Region] = "Madagascar" AND X IN ("N", "Y")
    GROUP BY [Location], X
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    1,180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query Assistance

    Quote Originally Posted by Juan Pablo González View Post
    Something like this maybe?

    Code:
    SELECT [Location], X, COUNT(X) AS CountOfX
    FROM TempQuery
    WHERE [Region] = "Madagascar" AND X IN ("N", "Y")
    GROUP BY [Location], X
    No, basically, the values in X are either N or Y, so I'm trying to show the split of the # of N's and the # of Y's.
    If the world didn't suck, we'd all fall off.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,475
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Query Assistance

    This looks good to me:
    Code:
    SELECT [Location], X, COUNT(X) AS CountOfX
    FROM TempQuery
    WHERE [Region] = "Madagascar" AND X IN ("N", "Y")
    GROUP BY [Location], X
    It will group all Madagascar fields into either Y or N, and report the count of each.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,155
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Query Assistance

    Or maybe you're after a cross tab? Untested
    Code:
    TRANSFORM COUNT(X)
    SELECT Location
    FROM TempQuery
    GROUP BY Location
    PIVOT X
    Add the "WHERE Location = 'Madagascar'" or similar criteria between the FROM & GROUP clauses

    Alternative final clause : PIVOT IN ('N', 'Y')

    cheers

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    1,180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query Assistance

    Quote Originally Posted by Fazza View Post
    Or maybe you're after a cross tab? Untested
    Code:
    TRANSFORM COUNT(X)
    SELECT Location
    FROM TempQuery
    GROUP BY Location
    PIVOT X
    cheers
    I kinda like the Pivot, but is it possible to add addition columns, like [Employee] to Location and the Y/N counts?
    If the world didn't suck, we'd all fall off.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,475
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Query Assistance

    Hi,
    I rarely write crosstab SQL by hand but if you switch into design view, it is usually quite easy to add "row" fields to the crosstab. However, this will affect the way you group the counts - it will be counts of Y and N for Madagascar by employee.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,155
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Query Assistance

    Quote Originally Posted by cstimart View Post
    I kinda like the Pivot, but is it possible to add addition columns, like [Employee] to Location and the Y/N counts?
    For additional columns like [Employee] I assume that is like an Excel pivot table row field. If so, add to the SELECT and GROUP BY clauses,

    Code:
    TRANSFORM COUNT(X)
    SELECT Location, Employee
    FROM TempQuery
    GROUP BY Location, Employee
    PIVOT X
    Maybe an Excel pivot table would be suitable?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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