Sql query help - group by problems in PostgreSQL

Hello experts,

I'm making an sql query that will run on a Postgresql DB and I need some help.

My query will give me the following data

YEAR ---- MONTH ---- COUNT

This information is grouped on year and month obviously and covers a few years. My problem is that I only wish to obtain three lines from each year, the three lines with the highest count that year. Is there a simple way of doing this ?
MannsiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

k_murli_krishnaCommented:
Your question is not completely clear. Of what column you are taking count? I am assuming name. Also, assuming that groups of name will be formed based on YEAR and MONTH and count will of these groups.
SELECT YEAR, MONTH, COUNT(NAME) FROM TABLE T GROUP BY YEAR, MONTH LIMIT 3;

The above query gives you top 3 records containing 3 counts for 3 different year + month combination. If you want top 3 counts for a given year and like this records for all years:
SELECT YEAR, MONTH, COUNT(NAME) FROM TABLE T GROUP BY YEAR, MONTH HAVING COUNT(1) <= 3;
You can keep just YEAR in SELECT list and GROUP BY and ignore MONTH if that suits you.
0
MannsiAuthor Commented:
hmmm...
Using the Limit function does not work for me but the description of the second example is exactly what I need. But when I enter HAVING COUNT(1) <= 3 I dont' get what I need at all. It just gives me my lowest count results
0
MannsiAuthor Commented:
Please ask me if my question is not clear. The second worded example from k_murli_krishna was spot on regarding my problem.
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

k_murli_krishnaCommented:
This query should work better:
SELECT YEAR, MONTH, COUNT(NAME) AS CNT FROM TABLE T GROUP BY YEAR, MONTH HAVING COUNT(1) <= 3 ORDER BY CNT DESC;
You may use ORDER BY 3 instead of ORDER BY CNT

If that does not work, try this:
SELECT YEAR, MONTH, CNT FROM (
SELECT YEAR, MONTH, COUNT(NAME) AS CNT FROM TABLE T GROUP BY YEAR, MONTH ORDER BY CNT DESC) IQ GROUP BY YEAR, MONTH, CNT  HAVING COUNT(1) <= 3;

LIMIT will give the number of records as its argument. But what you seek is 3 highest count records year wise. Let me know what problem you face if you still face any. I do not have postgre SQL to actually work it out. In case of syntax error, please correct it but seeing SQL documentation.
0
MannsiAuthor Commented:
What you are proposing does not work.
HAVING COUNT(1) <= 3 does not seem to work, at least not in Postgresql .
In your second Select statement there is no LIMIT and I don't know what IQ is.
0
k_murli_krishnaCommented:
IQ is alias name for inner query. Are you sure it is not the COUNT(NAME) which is failing since NAME column does not exist. Just try a valid column instead. See these links:
http://pgsqld.active-venture.com/queries.html
http://pgsqld.active-venture.com/queries-table-expressions.html#QUERIES-GROUP

From 2nd link, subqueries will be:
FROM (SELECT * FROM table1) AS alias_name
So, you need to write it like AS IQ instead of just IQ.
Also, one example of GROUP BY with HAVING:
SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;

Please let me know if this works and what column are you taking count on per year so that you need the highest 3 counts on a yearly basis. Just GROUP BY YEAR may be enough. So, you may need to discard the month from SELECT list as well as GROUP BY.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.