Count on 2 columns

[Henson4004]

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.

[r937]

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?

[Henson4004]

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

[r937]

replace COUNT(master_project) with COUNT(DISTINCT master_project)

[Henson4004]

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.

[r937]

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

[Henson4004]

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.

[r937]

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

[Henson4004]

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.

[r937]

i'm sorry, my head just exploded

[K. Wolfe]

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

[gk53]

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