• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

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 ?
0
Mannsi
Asked:
Mannsi
  • 3
  • 3
1 Solution
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now