vendredi 23 janvier 2015

SQLite returning groups with same number of rows in Android

I have below SQLite query:



select
product._id
supermarket._id
datetime(price.timestamp,'localtime') as LOCAL_TIMESTAMP,
price.price
from price inner join product on price.productid = product._id
inner join supermarket on price.supermarketid = supermarket._id

order by price.productid, price.supermarketid, price.timestamp


Rows are ordered by productid, supermarketid and timestamp. Doing so they are grouped but not all pairs products-supermarket have the same timestamps so is it possible to obtain all pairs products-supermarket with the same number of timestamps? If a pair product-supermarket has not a timestamp, then set its price to 0 in the returned result.


For example, above query may return:



Product_A Supermarket_A "2014-03-10 00:00:00" 2.3
Product_A SUpermarket_A "2014-04-10 00:00:00" 15.0
Product_A SUpermarket_A "2014-04-20 00:00:00" 10.5

Product_B Supermarket_A "2014-01-01 00:00:00" 23.3
Product_B SUpermarket_A "2014-05-21 00:00:00" 1.0


and I would like to obtain:



Product_A Supermarket_A "2014-01-01 00:00:00" 0.0
Product_A Supermarket_A "2014-03-10 00:00:00" 2.3
Product_A SUpermarket_A "2014-04-10 00:00:00" 15.0
Product_A SUpermarket_A "2014-04-20 00:00:00" 10.5
Product_A SUpermarket_A "2014-05-21 00:00:00" 0.0

Product_B Supermarket_A "2014-01-01 00:00:00" 23.3
Product_B Supermarket_A "2014-03-10 00:00:00" 0.0
Product_B Supermarket_A "2014-04-10 00:00:00" 0.0
Product_B Supermarket_A "2014-04-20 00:00:00" 0.0
Product_B SUpermarket_A "2014-05-21 00:00:00" 1.0


In each product-supermarket pair appears all the timestamps (like an union). If a product-supermarket pair has not the timestamp, it is created and its price set to 0.0.


Is it possible to do in SQL?


Aucun commentaire:

Enregistrer un commentaire