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?
 
k_murli_krishnaConnect With a Mentor Commented:
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.