SQL Count by equal columns Query

Question

I have this table:

subscriberID | date | segmentID | Counter
------------------------------------------
1            | 1.1  |    2      |    3 
1            | 2.1  |    4      |    2 
1            | 3.1  |    4      |    5 
2            | 1.1  |    1      |    12 
2            | 2.1  |    1      |    1
2            | 3.1  |    2      |    10 
3            | 1.1  |    2      |    4

I have to write SQL Query that does:

Get the top 3 most common segmentID's (by counter) for a given subscriberID.

can anyone help me with that? Thanks.

juergen d · Accepted Answer · 2014-04-05 12:10:27Z

1

select segmentID
from your_table
where subscriberID = 123
group by segmentID
order by sum(counter) desc

To get only 3 records you have to limit your result. Depending on your DB engine that could be top 3 or limit 3 or rownum <= 3.

answered Apr 5, 2014 at 12:10

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

1 Comment

juergen d Over a year ago

I just changed it using sum(counter). Not sure if that is what you want instead.

Collectives™ on Stack Overflow

SQL Count by equal columns Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related