0

So I have a table called violators, and I have 2 columns: Violation and Date. Violators:

---------------------------
Violation     | Date
---------------------------
overspeeding  | 2016-05-05
overloading   | 2016-05-07
overspeeding  | 2016-05-05
drunk driving | 2016-05-03

I want to know how many times the overspeeding occured in 2016-05-05, So the answer should be: 2016-05-05 - overspeeding - 2

And if I want to know, how many times the oversloading occured in 2016-05-07, The answer should be: 2016-05-07 - overloading - 1

1000111
  • 13,169
  • 2
  • 28
  • 37
Aenon T. Cunanan
  • 19
  • 1
    Hint: `WHERE`, `COUNT(*)`. – Gordon Linoff Aug 06 '16 at 13:39
  • Sorry, I am just new to MySql, I just started studying it earlier. My question is just part of my program. Other part of my program is to count the number of times an item occured. For example, Overspeeding occured twice, and I did it already. Another is to show a violation that occured in a particular date. I also did it already. But I am having a problem showing the number of times an item occurs in a particular date. – Aenon T. Cunanan Aug 06 '16 at 13:41

2 Answers2

1

there will be an entry in your table against every overspeeding record.

so execute a sql query (e.g with php)

$resultset = select * from tablename where Violation ='overspeeding' And Date ='2016-05-05'

this will give you two record so simply apply a count function on result set.in mysql with php ,it will look like this

$temp_variable_no_of_overspeeding =mysql_num_rows($resultset); 
// or you can use count function if don't need those values

so will have 2 in your temp variable

i guess you are using java so do like this

ResultSet rs = ps.executeQuery();
int rowcount = 0;
if (rs.last()) {
  rowcount = rs.getRow();
  rs.beforeFirst(); // required to read data in while later . not rs.first() 
                    //because the rs.next() below will move on 2nd element ,
                    //you will miss the first element
}
while (rs.next()) {
  // do your standard per row stuff
}
Pavneet_Singh
  • 36,884
  • 5
  • 53
  • 68
  • Thanks! I should output it as a graph in GUI using java. I can already graph other parts of my program. But still having a problem here. I should graph it with the dates in my x axis and number of occurences in y axis. Do you have an idea how to do it? I am using javafx – Aenon T. Cunanan Aug 06 '16 at 13:52
  • if you wanna graph it then go for first query then instead of count , traverse the result-set dynamically with total count ,fetch data and use where ever you want – Pavneet_Singh Aug 06 '16 at 13:57
  • Update: I can already show the dates where the violation occured. But I still can't count how many times the violation occured at a particular date. Can someone help me what the query is? I really don't have any idea what query to use. – Aenon T. Cunanan Aug 06 '16 at 14:09
0

I already fixed the problem: So here's the Sql query:

select Violation, count(Violation), Date_Violated from violators group by Violation, Date_Violated

Thank you guys for the help!

Aenon T. Cunanan
  • 19