Distinct count with multiple columns

Question

I've been looking around and can't find an answer to this question on finding a distinct match. I have a table that looks like

ID       Category     Relevancy
192820  273003000   2
242567  273003000   2
510711  273003000   2
510711  273003002   34
542178  273003000   1
542178  273003002   2
688833  273003000   3
734917  273003002   2
888879  273003000   1
891624  273003002   3

So for each id they can have multiple categories and the relevancy is what what category number (1 is the first category, 2 second, 34 the thirty fourth category for that id).

I want to query where I can search the count of the Category but if the id has already been counted for a category it will not be counted.

So for this the answer would look like:

Category         Count  
273003000         6
273003002         2

Thanks for the help!!

I'm not sure if this is what you want: select category, sum(Relevancy) as sum_rev, count(*) as ct from tablename group by category. I don't know what this means: "I want to query where I can search the count of the Category but if the id has already been counted for a category it will not be counted." — gloomy.penguin
– gloomy.penguin, Commented Sep 6, 2013 at 17:42
@peter, I really did a bad job explaining myself. So I have a list of category's and want to get a count of id's within each category but some of the id's have more than one category that I am searching on so I don't want that id getting counted twice. So basically my query right now looks like .... Select category,count(distinct pid) from TABLE where category in (.......) group by category. With this it is counting (in the original example) 27300300 = 6 and 273003002 = 4 — user2755209
– user2755209, Commented Sep 6, 2013 at 22:25

lc. · Accepted Answer · 2013-09-06 17:41:45Z

3

It sounds like you want to count only the records with minimum Relevancy for each ID. Therefore, you can do (SQL Fiddle example):

SELECT Category, COUNT(1)
FROM Table1 t1
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table1 t2
    WHERE t2.ID = t1.ID
    AND t2.Relevancy < t1.Relevancy
)
GROUP BY Category

answered Sep 6, 2013 at 17:41

lc.

117k21 gold badges164 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tricky12 · Accepted Answer · 2013-09-06 17:45:26Z

0

This should do the trick since you do not care about the Relevancy value itself, but just the count:

select a.category, count(a.relevancy) as count
from (select *
      from test
      group by id) as a
group by a.category;

SQLFiddle

answered Sep 6, 2013 at 17:45

Tricky12

6,8301 gold badge30 silver badges36 bronze badges

Collectives™ on Stack Overflow

Distinct count with multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related