SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count on 2 columns

    If I use count on one column as: SELECT master_project, count(master_project ) AS mycounter FROM property GROUP BY master_project

    I am getting distinct rows with their counters. I want to use same for 2 columns by one query.

    When I try to apply count on 2 columns as

    SELECT master_project, count(master_project ) AS mycounter, project, count(project ) AS myprojectcounter
    FROM property GROUP BY master_project, project

    In this case I don't get my expected results where as I want to see both columns having distinct records with counters regardless of each other.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Henson4004 View Post
    In this case I don't get my expected results ...
    what are your expected results?

    are you perhaps looking to count the distinct values in each column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes exactly I am looking to count the distinct values in each column.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    replace COUNT(master_project) with COUNT(DISTINCT master_project)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help. I have tried as:

    SELECT master_project, count(DISTINCT master_project ) AS mycounter, project, count(DISTINCT project ) AS myprojectcounter
    FROM property GROUP BY master_project, project

    I want to see each distinct value with its counter for both columns regardless of each other where as GROUP BY master_project, project doesn't show me expected results.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't understand

    perhaps you could explain by showing several rows of sample data from the property table, and then show the results that you want the query to produce
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    master_project mycounter project myprojectcounter
    1 181 10 181
    2 80 26 80
    3 268 43 268
    4 129 28 129
    5 84 153 84
    6 9 65 9
    8 11 100 11
    9 40 72 40
    10 166 51 166

    First and third columns showing distinct values and mycounter which is counting for master_project is correct but myprojectcounter which should count for project column against its each value is incorrect e.g. there are only 8 records having 10 projects where as it is showing 181. Please help.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, that doesn't help

    those are not rows of the property table, those are the results of your (not working) query

    i wanted to see actual rows of the table to understand what you're trying to count

    meanwhile, how about another approach... create two separate queries, one for each count
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    create two separate queries, one for each count
    I am sorry I am unable to explain well. I am creating left side filters for my website page. Idea is that each filter will have its own counter and there are about 15 filters. I have already done it through 15 different queries which is making my page too slow and don't think it is a better approach. I am trying to build a query which can filter my 15 fields of same table with counter of each filter. I want to filter rows with respect to certain columns like column A has 20 distinct values and each value has its own counter, similarly for column B and C and so on.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, my head just exploded
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    How about showing some sample data right from the table before you query.

  12. #12
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just publish snapshot from your data even if it is multiple tables a few records from each, so we can understand what you need


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
  •