0

I have a table that has around 100,000 records. It has a column named data_key, data_value and timestamp. Now if datakey = 'crop'; I've stored the corresponding data_value in a separate table. There are 52 different types of crops. So, I've two tables now 'Eventlogs' and 'Crops'.

Now I need to get the timestamp for all the crops searched on each day. I've written a query 

Select date(a.timestamp) as Date, a.datavalue as Cropname, count(a.datavalue) as Quantity 
from eventlog a, crops b 
where a.datavalue = b.datavalue;

But it is only showing the first occurrence of any crop for any day.

So, if tomatoes and apples were sold on 24th September the Quantity is the sum of all the crops but the crop displayed is only the first one.

Sample Data is like

Timestamp | Datakey | DataValue
23-04-2002| Crop    | Tomato
23-04-2002| Crop    | Tomato
23-04-2002| Crop    | Apple
24-04-2002| Crop    | Apple
24-04-2002| Crop    | Tomato
26-04-2002| Crop    | Banana
26-04-2002| Crop    | Banana
27-04-2002| Crop    | Tomato

Data of the crops table would be like

    Crops
------------
    Apple
    Tomato
    Banana

Now I need to produce the output like

Timestamp | Crop_name | Quantity
23-04-2002| Tomato    | 2
23-04-2002| Apple     | 1
24-04-2002| Apple     | 1
24-04-2002| Tomato    | 1    
26-04-2002| Banana    | 2
27-04-2002| Tomato    | 1
Will
  • 69
  • 10
  • 3
    Providing some sample table and expected output would be great. – Ullas Feb 13 '18 at 07:03
  • 3
    Also include your table structure. The basic problem is that you are calling `COUNT` without `GROUP BY`. This means that a single record will be returned with a count over the entire table (not what you want). – Tim Biegeleisen Feb 13 '18 at 07:07
  • Hi. Since you want the count per each group of rows with the same subrow values, group by the columns for the shared subrows. Decide what should be returned if there are no rows, because that affects teh query too. Next time google many clear concise specific variants/versions/phrasings of your question/problem/goal & read many answers per each. – philipxy Feb 13 '18 at 07:17
  • Possible duplicate of [How to use count and group by at the same select statement](https://stackoverflow.com/q/2722408/3404097) – philipxy Feb 13 '18 at 07:19

1 Answers1

0

Try this:

SELECT  STR_TO_DATE(A.`Timestamp`,'%Y-%m-%d') `Timestamp`, A.DataValue 
Crop_name, COUNT(*) Quantity
FROM eventlog A INNER JOIN crops B 
ON A.DataValue=B.DataValue
WHERE A.Datakey='Crop'
GROUP BY STR_TO_DATE(A.`Timestamp`,'%Y-%m-%d'), A.DataValue
ORDER BY STR_TO_DATE(A.`Timestamp`,'%Y-%m-%d'), COUNT(*) DESC;

See MySQL Join Made Easy AND MySQL GROUP BY

Here is the DEMO

cdaiga
  • 4,861
  • 3
  • 22
  • 42
  • It is showing each crop individually. It should be the sum of all the crop occurrences for that date. – Will Feb 13 '18 at 07:35
  • Can you create your sample database on [sql fiddle](http://sqlfiddle.com/), and share so that I can test? – cdaiga Feb 13 '18 at 07:45
  • @Will The output in the question shows separate rows for each crop. – Barmar Feb 13 '18 at 08:11
  • `A.Datakey = 'Crop'` should be in `WHERE`, not `ON`. MySQL treats them equivalently, but `ON` should just be used for relationships between tables (except for outer joins where you need to filter the child table). – Barmar Feb 13 '18 at 08:14
  • No need for a subquery. Just put `WHERE A.Datakey = 'Crop'` in the main query. – Barmar Feb 13 '18 at 08:35
  • Works perfectly... Thanks – Will Feb 13 '18 at 08:41