Get sum using DATE_FORMAT() and IN

50
February 12, 2019, at 11:40 AM

I've got a query that looks a little bit like this :

SELECT sum(X) as qty 
FROM ...
WHERE ...
AND DATE_FORMAT(date,'%Y%m%d') IN (20100601,20100701,20100801,20100901,20101001)

This obviously is giving me a result that looks like :

qty
226761

But what I'm looking to get is the quantity for each separate date format :

qty      date
16000   20100601
57000   20100701
23000   20100801
30000   20100901
72000   20101001

I'm new to SQL so I have no idea how to go about getting that specific format. I'm just looking for help of the algorithms, you can use random table names or row names I don't mind. I hope my question is clear!

Answer 1

You need to group by DATE_FORMAT(date,'%Y%m%d')

SELECT DATE_FORMAT(date,'%Y%m%d') datex, sum(X) as qty 
FROM ...
WHERE ...
AND date IN ('20100601','20100701','20100801','20100901','20101001')
GROUP BY date 
Answer 2

You need a GROUP BY:

SELECT date(date), sum(X) as qty 
FROM ...
WHERE ... AND
      date(date) IN ('2010-06-01', '2010-07-01', '2010-08-01', '2010-09-01', '2010-10-01')
GROUP BY date(date);

You'll notice that I removed the date_format(). Converting dates to strings is a bad habit and there is no need to do this. If date has no time component (as suggested by its name), then date() is not even needed.

EDIT:

The use of date_format() suggests that your dates are stored as dates. If this is not true, then you can simply do:

SELECT date, sum(X) as qty 
FROM ...
WHERE ... AND
      date IN ('20100601', '20100701', '20100801', '20100901', '20101001')
GROUP BY date;

Drop the single quotes if the date are numbers.

READ ALSO
In select query include placeholder results where no direct result was found

In select query include placeholder results where no direct result was found

I have a table of race resultsNot all competitors enter every single race, but I would like to include a table which shows those races they didn't enter with blanks and a hyphen "-" value in the result column

54
mysql - current_timestamp and null

mysql - current_timestamp and null

I have question about mysql configurationI have the same database on AWS and local apache server on ubuntu

38
How to export a database (.sql) file to remote database through wifi? [on hold]

How to export a database (.sql) file to remote database through wifi? [on hold]

I have external database where I have export thesql file to my local now I want to import the same database through terminal by using wifi to another

35