CODEWITHSUNDEEP
mysqlsqlselect
Dan
Dan

Reputation: 393

How to do the SELECT with a Variables in MySQL?

I have a table myTable

   Id       userID  month year login day
   1628325  812467  1     2015  1    6
   1628326  969054  1     2015  2    9
   1628327  812467  2     2015  1    27
   1628328  811602  2     2015  1    27
   1628329  952056  3     2015  3    2
   1628330  1082029 3     2015  5    2
   1628337  952056  3     2015  1    3
   1628338  952056  3     2015  6    4
   1628339  812467  3     2015  2    4
   1628340  1082029 3     2015  2    4
   1628341  811602  3     2015  4    4
   1628349  1081988 3     2015  1    4

I need to count the total number of login by date. I have 2 incoming variables :startDate and :endDate. So select should be something like:

SELECT DATE_FORMAT(Date, '%Y/%m/%d') "Day", sum(login) FROM myTable WHERE Date >=:startDate AND Date<:endDate ORDER BY Date DESC

How to implement it?

The variables comes from jsp page

From the debug mode in Eclipse I can see that :startDate and :endDate variables that comes from jsp page - have the following format:

Wed Jul 01 00:00:00 EEST 2015, Tue Jul 28 00:00:00 EEST 2015

When I run the report it is not showing nothing. Can be the problem with a time format representation? (%y-%m-%d) or (%y/%m/%d) or ..

Upvotes: 1

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can construct a date from the components. One way is using str_to_date(). However, you really should store the date as a date, rather than the individual components. MySQL has good support for dates, so you should use these features of the database.

Here is one way:

SELECT str_to_date(concat_ws('-', year, month, day), '%Y-%m-%d') as date, sum(login)
FROM myTable
WHERE str_to_date(concat_ws('-', year, month, day), '%Y-%m-%d') >= :startDate AND
      str_to_date(concat_ws('-', year, month, day), '%Y-%m-%d') < :endDate 
GROUP BY date
ORDER BY date;

An alternative that requires more processing but is easier to write is to use having:

SELECT str_to_date(concat_ws('-', year, month, day), '%Y-%m-%d') as date, sum(login)
FROM myTable
GROUP BY date
HAVING date >= :startDate and date < :endDate
ORDER BY date;

This is less efficient because it filters the data after aggregating the entire table.

Upvotes: 1

Related Questions