CODEWITHSUNDEEP
mysql
Raj
Raj

Reputation: 993

MySQL query for total number of cars purchased per month

I need a help to find total number of cars purchased based on every month. I have a table(purchase) setup like this (simplified for example):

  sno
  country
  purchased_date(yyyy-mm-dd)
  make
  model

Now I want to generate a report with the total number of cars purchased every month, and here's my query:

SELECT COUNT(`country`), `model`, `make`, `purchase_date`, `country`
FROM charmy_purchase 
WHERE `country` != '' 
GROUP BY model

It produces the wrong report.

Upvotes: 1

Views: 6043

Answers (1)

Arion
Arion

Reputation: 31249

Maybe something like this:

SELECT 
    COUNT( `country` ) , 
    `model` , 
    MONTH(purchase_date) AS Month
FROM 
    charmy_purchase 
WHERE 
    `country` != '' 
GROUP BY 
   model,
   MONTH(purchase_date)

EDIT

If you have different years. Then you might consider grouping on year as well. Like this:

SELECT 
    COUNT( `country` ) , 
    `model` , 
    MONTH(purchase_date) AS Month,
    YEAR(purchase_date) AS year
FROM 
    charmy_purchase 
WHERE 
    `country` != '' 
GROUP BY 
   model,
   MONTH(purchase_date),
   YEAR(purchase_date)

Useful references:

Upvotes: 4

Related Questions