0

I have a table called commodities where I have nature_of_goodscolumn in it. nature_of_goods contains all the different types of goods like machinery, household etc.

I have another column called as created at which contains the date and time in timestamp format.

I need to find the maximum goods took place in particular month.

I have tried the following query:

select max(mycount)
from   (select   nature_of_goods, count(nature_of_goods) AS mycount
        from     commodities
        group by month(created_at));
Patrick Mevzek
  • 1,141
  • 1
  • 10
  • 19
Sai kiran
  • 1
  • 2

1 Answers1

0

You need to GROUP BY nature_of_goods, if the counts should be separated by their values. To only show results of a particular month you have to put that in the WHERE, otherwise all months will be used.

Try something like this:

select max(mycount)
from   (select   count(nature_of_goods) AS mycount
        from     commodities
        where    month(created_at) = <insert desired month here>
        group by nature_of_goods) x;

.

sticky bit
  • 4,834
  • 2
  • 13
  • 19
  • 1
    I think she wants it for at least an entire year? So, it's a bit more tricky than that! – Vérace Apr 30 '18 at 12:02
  • @Vérace: Maybe I misunderstood the question. I will edit or delete my answer, as soon as the OP states that my understanding was wrong. – sticky bit Apr 30 '18 at 13:04