Counting data falling within certain month in mySQL

Question

Hi I'm having trouble sorting image data by month, I'd like to be able to tell how many images were created, updated and deleted within a certain month by getting the count and grouping then by the month. I have two tables with image data in. Both tables have 'createdAt', 'updatedAt' and 'deletedAt' columns so what I did was date format the columns of both tables and union them to have all of the data together but I'm having trouble getting the count of the data. Here is what I have so far:

SELECT DATE_FORMAT(`createdAt`, "%M %Y"), DATE_FORMAT(`updatedAt`, "%M %Y"), DATE_FORMAT(`deletedAt`, "%M %Y") FROM harvest_sample_images UNION SELECT DATE_FORMAT(`createdAt`, "%M %Y"), DATE_FORMAT(`updatedAt`, "%M %Y"), DATE_FORMAT(`deletedAt`, "%M %Y") FROM plant_sample_images

Any help would be appreciated.

SELECT DATE_FORMAT(`createdAt`, "%M %Y") DAT, count(*) AS `created`,
(SELECT DATE_FORMAT(`updatedAt`, "%M %Y") DAT, COUNT(*)) AS `updated`,
(SELECT DATE_FORMAT(`deletedAt`, "%M %Y") DAT COUNT(*)) AS `deleted`
FROM (SELECT `createdAt`, `updatedAt`, `deletedAt` FROM harvest_sample_images UNION SELECT `createdAt`, `updatedAt`, `deletedAt` FROM plant_sample_images)
GROUP BY DATE_FORMAT(`created`, "M% %Y")

I have tried this now.

So I might have found another way of sorting the images info into each respective month with the count as well as displaying the month it falls within.

SELECT DATE_FORMAT(`timestamp_date`, "%M %Y") AS `Month`,
(SELECT COUNT(*) FROM (SELECT `createdAt` FROM harvest_sample_images UNION ALL SELECT `createdAt` FROM plant_sample_images) AS `images_created` WHERE DATE_FORMAT(images_created.`createdAt`, "%m %y") = DATE_FORMAT(`timestamp_date`, "%m %y")) AS `Created`,
(SELECT COUNT(*) FROM (SELECT `updatedAt` FROM harvest_sample_images UNION ALL SELECT `updatedAt` FROM plant_sample_images) AS `images_updated` WHERE DATE_FORMAT(images_updated.`updatedAt`, "%m %y") = DATE_FORMAT(`timestamp_date`, "%m %y")) AS `Updated`,
(SELECT COUNT(*) FROM (SELECT `deletedAt` FROM harvest_sample_images UNION ALL SELECT `deletedAt` FROM plant_sample_images) As `images_deleted` WHERE DATE_FORMAT(images_deleted.`deletedAt`, "%m %y") = DATE_FORMAT(`timestamp_date`, "%m %y")) AS `Deleted`
FROM table_dates

So what I'm doing now is , I have created another table with list of dates ranging from the start of the data to the present. I then loop through the data and check if the formatted date of the table_dates if equal to the formatted date of the union of the two images tables date. I the get the count and add it as a column in the new table. Thoughts are appreciated

ScaisEdge · Accepted Answer · 2019-05-07T09:40:22.290

1

You need count and group by

select  year(createdAt) year , month(createdAt) month, count(*)
from  my_table  
group by   year(createdAt), month(createdAt)

or

SELECT DATE_FORMAT(`createdAt`, "%M %Y")  DAT, count(*)
from  my_table  
group by   DATE_FORMAT(`createdAt`, "%M %Y")

for the 3 count you could use join

select t1.year_month, t1.count_create, t2.count_update, t3count_delete
from (
  SELECT DATE_FORMAT(`createdAt`, "%M %Y") year_month , count(*) count_create 
  from  my_table  
  group by   DATE_FORMAT(`createdAt`, "%M %Y")
) t1 
left join (
  SELECT DATE_FORMAT(`updatedAt`, "%M %Y") year_month , count(*) count_update 
  from  my_table  
  group by   DATE_FORMAT(`updatedAt`, "%M %Y")

) t2 ON t1.year_month = t2.year_month
left join (
  SELECT DATE_FORMAT(`deletedAt`, "%M %Y") year_month , count(*) count_delete 
  from  my_table  
  group by   DATE_FORMAT(`deletedAt`, "%M %Y")

) t3 ON t1.year_month = t3.year_month

edited May 07 '19 at 09:40

answered May 07 '19 at 09:32

ScaisEdge

131,976
10
91
107

Will it work with all three columns ? I need it to be a view – Sir Catzilla May 07 '19 at 09:33
SELECT DATE_FORMAT(`createdAt`, "%M %Y") DAT, count(*) AS `created`, (SELECT DATE_FORMAT(`updatedAt`, "%M %Y") DAT, COUNT(*)) AS `updated`, (SELECT DATE_FORMAT(`deletedAt`, "%M %Y") DAT COUNT(*)) AS `deleted` FROM (SELECT `createdAt`, `updatedAt`, `deletedAt` FROM harvest_sample_images UNION SELECT `createdAt`, `updatedAt`, `deletedAt` FROM plant_sample_images) GROUP BY DATE_FORMAT(`createdAt`, "M% %Y") – Sir Catzilla May 07 '19 at 09:38
The information isn't just from one table its from two – Sir Catzilla May 07 '19 at 09:42
@Miaan SO is not a coding service .. i think mi answer give you a lot of info about the way for buil the result .. you need .. based on my answer you can or use a union for the second table and obtain a select for the united rows or add 3 others left join for the 3 column related to the second table .. – ScaisEdge May 07 '19 at 09:44
I may have found another way of doing what I wanted Input is appreciated – Sir Catzilla May 13 '19 at 09:54
if you have a table with the date range .. then instead of a loop you could use a left join from the table with date range and the query suggested in my answer .. . – ScaisEdge May 13 '19 at 10:04
what would the difference be between a left join and a select? – Sir Catzilla May 13 '19 at 10:10
1

a select .. is the part of a query that set the columns to be retrived by the query .. a join is the part of a query that define a relation between tables ...https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins – ScaisEdge May 13 '19 at 10:40

Counting data falling within certain month in mySQL

1 Answers1