CODEWITHSUNDEEP
mysqlsql-serverselect
A M
A M

Reputation: 3

Getting duplicate records to show in same row

Hello I have a table that Has a movie name, the date it was released, the cost and the revenue. I am trying to write a query to display how many movies were released per month

CREATE TABLE movies_200249154(

movieName VARCHAR(30),
releaseDate DATE,
costInMillions INT (20),
revenueInMillions INT (20)

);

Above is my table

Here is my current query

SELECT DISTINCTROW (monthname(releaseDate)) AS 'Month released', COUNT(*)movieName
FROM movies_200249154
GROUP BY movieName;

Right now my query will display the month, and the count for the movie (always being 1) and shows duplicates. If I use SELECT DISTINCT it only displays 1 movie per month. Seemingly forgetting about the other data.

What I need is for my query to display the month AND the amount of movies released that month. For example it currently repeats June 4 times with a count of 1. The query I need would display June and a count of 4.

Any help is greatly appreciated

Upvotes: 0

Views: 42

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can use MONTH function of MySQL and group by with count to get the required value, e.g.:

SELECT MONTH(releaseDate), count(*)
FROM movies
GROUP BY MONTH(releaseDate);

Upvotes: 2

Related Questions