How to group by the query using an attribute from another table in MySQL

Asad Hussain 0
Viewed 25

I have a table of

I have a table of Month with the following structure,

 1. MonthID (Primary Key) 
 2. MonthName

I have another Table of Date with the following structure,

 1. DateID (Primary Key) 
 2. Day 
 3. Month (Foreign Key, references to MonthID in Month Table)
 4. Year

Lastly, there's a table with the name of Sales,

 1. Date (Foreign key, references to DateID in Date Table) 
 2. SaleItem
 3. SaleAmount

My required query is the Count of Sales done for every month

So, what I want is to get the count of Sales Table rows but group by those counts with the MonthID (that is an attribute in the Date Table) and also extract the Name of the month from the basic Month Table.

The expected outcome would be something similar to this,

Month Sales
January 15
February 0
March 221
April 11
mysql sql
1 Answers

Using the below query you can get the result as per above

SELECT "Month"."MonthName" AS "Month", count("Sales".*) AS "Sales" FROM "Month"
LEFT JOIN "Date" ON "Date"."Month" = "Month"."MonthID"
LEFT JOIN "Sales" ON "Sales"."Date" = "Date"."DateID"
GROUP BY "Month"."MonthID", "Month"."MonthName"
ORDER BY "Month"."MonthID" ASC;
Mukesh Singh Thakur0
Related