CODEWITHSUNDEEP
mysql
bob
bob

Reputation: 11

Trouble outputting table with MySQL query

I have a MySQL database with the following columns:

bookingdatetime (datetime)
promo
pickuploc
quotedprice

I want to query the table so I can see how much in dollars were spent using each specific promo code in only a specific city. I want the output to look like this:

Month/Year      Promo     Amount Sent
01/20           test1     100
02/20           test1     50
02/20           test2     50
02/20           test3     50

Here is a fiddle: http://sqlfiddle.com/#!9/feccb3/1

As you can see, my query is not giving me the correct output. I need it to count each promo used in each month. So I can get an accurate idea of what is generating me the most profit.

Here is my query for reference:

SELECT DISTINCT DATE_FORMAT(bookingdatetime, '%m/%y') AS mont_year,
promo as Promo,
SUM(quotedprice) AS Amount_Sent
FROM `reservations`
WHERE pickuploc = 'la' AND promo <> '' GROUP BY promo

Not great at MySQL, had lots of help on this and still not working. Please advise.

Upvotes: 1

Views: 26

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

The year-month is missing from the GROUP BY clause, and also you don't need to use DISTINCT:

SELECT
    DATE_FORMAT(bookingdatetime, '%Y-%m') AS year_month,
    promo AS Promo,
    SUM(quotedprice) AS Amount_Sent
FROM reservations
WHERE pickuploc = 'la' AND promo <> ''
GROUP BY
    DATE_FORMAT(bookingdatetime, '%Y-%m'),
    promo
ORDER BY
    DATE_FORMAT(bookingdatetime, '%Y-%m'),
    promo;

Upvotes: 1

Related Questions