-- Database schema
| customers : CustomerID [ INTEGER ] primary_key , First [ TEXT ] , Last [ TEXT ] , StreetAddress [ TEXT ] , City [ TEXT ] , State [ TEXT ] , ZipCode [ INTEGER ] , Email [ TEXT ] , PhoneNumber [ TEXT ] , FirstPurchaseDate [ DATE ] , SubscribedToEmailList [ TEXT ] , Gender [ TEXT ] | geolocation : LocationID [ INTEGER ] primary_key geolocation.LocationID = location.LocationID , Latitude [ REAL ] , Longitude [ REAL ] | location : LocationID [ INTEGER ] primary_key location.LocationID = geolocation.LocationID , LocationName [ TEXT ] , StreetAddress [ TEXT ] , City [ TEXT ] , State [ TEXT ] , ZipCode [ INTEGER ] | rootbeerbrand : BrandID [ INTEGER ] primary_key , BrandName [ TEXT ] , FirstBrewedYear [ INTEGER ] , BreweryName [ TEXT ] , City [ TEXT ] , State [ TEXT ] , Country [ TEXT ] , Description [ TEXT ] , CaneSugar [ TEXT ] , CornSyrup [ TEXT ] , Honey [ TEXT ] , ArtificialSweetener [ TEXT ] , Caffeinated [ TEXT ] , Alcoholic [ TEXT ] , AvailableInCans [ TEXT ] , AvailableInBottles [ TEXT ] , AvailableInKegs [ TEXT ] , Website [ TEXT ] , FacebookPage [ TEXT ] , Twitter [ TEXT ] , WholesaleCost [ REAL ] , CurrentRetailPrice [ REAL ] | rootbeer : RootBeerID [ INTEGER ] primary_key , BrandID [ INTEGER ] rootbeer.BrandID = rootbeerbrand.BrandID , ContainerType [ TEXT ] , LocationID [ INTEGER ] rootbeer.LocationID = geolocation.LocationID , PurchaseDate [ DATE ] | rootbeerreview : CustomerID [ INTEGER ] rootbeerreview.CustomerID = customers.CustomerID , BrandID [ INTEGER ] rootbeerreview.BrandID = rootbeerbrand.BrandID , StarRating [ INTEGER ] , ReviewDate [ DATE ] , Review [ TEXT ] | transaction : TransactionID [ INTEGER ] primary_key , CreditCardNumber [ INTEGER ] , CustomerID [ INTEGER ] transaction.CustomerID = customers.CustomerID , TransactionDate [ DATE ] , CreditCardType [ TEXT ] , LocationID [ INTEGER ] transaction.LocationID = location.LocationID , RootBeerID [ INTEGER ] transaction.RootBeerID = rootbeer.RootBeerID , PurchasePrice [ REAL ] |
-- -- List out root beer brand that is not caffeinated and not containing cane sugar. What is the total amount sold for this products?

SELECT T1.BrandName, SUM(T3.PurchasePrice) FROM rootbeerbrand AS T1 INNER JOIN rootbeer AS T2 ON T1.BrandID = T2.BrandID INNER JOIN `transaction` AS T3 ON T2.RootBeerID = T3.RootBeerID WHERE T1.CaneSugar = 'FALSE' AND T1.Caffeinated = 'FALSE' GROUP BY T1.BrandName;