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.

what are your expected results?

are you perhaps looking to count the distinct values in each column?

Yes exactly I am looking to count the distinct values in each column.

replace COUNT(master_project) with COUNT(DISTINCT master_project)

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.

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

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.

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

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.

i’m sorry, my head just exploded

How about showing some sample data right from the table before you query.

just publish snapshot from your data even if it is multiple tables a few records from each, so we can understand what you need