jonathanvx / sr24 Goto Github PK

Hi and thanks for this fantastic compilation of data.

I made a tutorial about how to set up the data (while I was doing it for myself, I thought it would be a good idea to share it):
http://localcode.wikidot.com/playing-with-a-food-database

Maybe github is not the right place to ask about how the underlying data works, but using your database I made the sum of nutr_value for a single food description (in this case: "blue cheese") focusing only in units that were in grams. As I understand it, the values are "per 100gr of food", so I expected a sum lower than 100. I got 177 instead. The error is more likely to be because of my interpretation of the data rather than an error in the database, but I wanted to ask in case you or anyone else understood what was going on here.

SELECT 
    sum(nutr_val)
FROM
    nutrient_data ndata
        JOIN
    nutrient_definition ndef ON ndata.nutr_no = ndef.nutr_no
        JOIN
    food_desc fdesc ON fdesc.ndb_no = ndata.ndb_no
        JOIN
    food_grp_desc fgrpdesc ON fgrpdesc.fdgrp_cd = fdesc.fdgrp_cd 
WHERE
    long_desc = 'Cheese, blue' and units_text = 'g';

Note: to run this query you need to first transform units (in blob in the original database) to unit_text (as varchar). As explained in my blog post, it's done like so:

ALTER TABLE nutrient_definition ADD units_text VARCHAR(10);
UPDATE nutrient_definition SET units_text=CAST(units AS CHAR(10000) CHARACTER SET latin1);