CODEWITHSUNDEEP
sql
Zax
Zax

Reputation: 491

Selecting and modifying a field in SQL

Let's say I have a table with name and birthday fields. I can use this SQL to return a table of items sharing the same birthday (e.g. 4 people have the birthday 4/8/1995):

SELECT DISTINCT "Birthday", COUNT("Birthday") as "FieldCount" 
FROM "test_main" Group BY "Birthday" Order By "FieldCount" DESC

But how can I modify the value that I select to ignore the year, e.g. get a count of birthdays by month, for example, Jan: 42 names, Feb: 28 names, etc

Thanks

Upvotes: 0

Views: 35

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

One way is to use built in functions to extract the year and month:

SELECT month(birthday), count(*) as FieldCount
FROM test_main
Group BY month(birthday)
Order By FieldCount DESC;

Notes:

  • There is no need for the distinct with a group by.
  • You do not need to surround everything with double quotes.
  • COUNT(*) should be find for what you want.

Upvotes: 1

juergen d
juergen d

Reputation: 204924

SELECT month(Birthday), COUNT(Birthday)
FROM test_main
Group BY month(Birthday) 
Order By COUNT(Birthday) DESC

Upvotes: 2

Related Questions