asked
Sequel pro: Comparing data between consecutive dates
I have a dataset of bushmeat consumption for a list of households. It has columns for date, household ID (hh_id), meal_type (breakfast, lunch, or dinner - values 1, 2, or 3), and the kg of bushmeat consumed by that household on that date. ![alt text][1] (see image to see the hh_id, date, and meat columns. meal_type is formatted in same way but couldn't be included in screenshot) I have 2 tasks. I need to COUNT the number of instances in which: 1. For the same household, bushmeat was consumed (meat > 0) at dinner (meal_type = 3) on one day (date) and at breakfast (meal_type = 1) the next day (date + 1). So here, I am trying to COUNT row pairs in which the first row is hh_id, date, meal_type = 3, meat >0 and the second row is hh_id, date + 1, meal_type = 1, meat >0 2. For the same household, bushmeat was consumed (meat>0) at breakfast (meal_type = 1) and dinner (meal_type = 3) on the SAME day Here, I am trying to COUNT row pairs in which the first row is hh_id, date, meal_type =1, meat >0 and the second row is hh_id, date, meal_type = 3 [1]: /storage/temp/3523-screen-shot-2016-06-27-at-93350-pm.png
Oh, and read this if you want to get quick answers to t-sql type questions:
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
This is data analysis for my research project - for work at an environmental health organization. However, I am completely new to sql and am struggling to pick up the syntax. Below, I list 2 lines of code that I have been playing around with. However, I have not come even close to getting at the core of my question. Any help would be greatly appreciated! SELECT COUNT(fonla) FROM hh_dietary_intake WHERE fonla>0 AND meal_type=3 SELECT hh_id,date,meal_type,fonla FROM hh_dietary_intake WHERE fonla>0 AND meal_type=3 ORDER BY hh_id,date;
Try this as the basis for your scripts. SELECT date, hh_id, COUNT(fonla) FROM hh_dietary_intake WHERE fonla>0 AND meal_type=3 GROUP BY date, hh_id ORDER BY date, hh_id This will give you your counts by date and household with a single total for each
19 People are following this question.
