#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Groupby query help


    I have a table with data as follows

    Source name address city
    File Y N N
    File N N Y
    DB Y N Y
    DB N Y N
    XML Y Y N

    I am trying to get output as follows

    Source Y/N CountName CountAddress
    File Y 1 0
    File N 1 2
    DB Y 1 1
    DB N 1 1
    XML Y 1 1
    XML N 0 0
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Please explain how those numbers should be calculated.

    And if you post sample data, please format it so that it is aligned (e.g. using a fixed font or [code] tags)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    formated table


    The number are calculated as follows

    For "File" as source, in Name "Y" has occured 1, so the count is 1. "N" has occured once for "File" source in name, so count is 1. I am trying to get a report kind of structure by grouping source and the "Y/N" to get the count. So, its get the count of Y and N for each source and each field(Name, address)
    but the catch is "Y/N", may not be present in some case .

    Code:
    Source	Name	Address	City
    File	Y	N	N
    File	N	N	Y
    DB	Y	N	Y
    DB	N	Y	N
    XML	Y	Y	N
    I am trying to get the occurence of Y and N for each source across each columns(such as name, address. First record says, in "File" as source, "Y" has occured once in Name column, but 0 times in Address column.etc.

    Code:
    Source	Y/N	CountOfName	CountOfAddress
    File	Y	1	0
    File	N	1	2
    DB	Y	1	1
    DB	N	1	1
    XML	Y	1	1
    XML	N	0	0

IMN logo majestic logo threadwatch logo seochat tools logo