On 02/04/2011 04:00 AM, sqlite-users-requ...@sqlite.org wrote: > On 2/3/2011 12:10 PM, Scott Baker wrote: >> > CREATE Table Customers ( >> > EntryID INTEGER PRIMARY KEY, >> > CustomerID INT, >> > Type ENUM >> > ); >> > >> > #1) Query for customers who*ONLY* bought apples > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple')>0 and sum(Type != 'Apple')=0; > >> > #2) Query for customers who bought apples*AND* bananas > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple')>0 and sum(Type = 'Banana')>0; > >> > #3) Query for customers who bought exactly 2 apples? > select CustomerID from Customers > group by CustomerID > having sum(Type = 'Apple') = 2; > > -- or > > select CustomerID from Customers > where Type = 'Apple' > group by CustomerID > having count(*) = 2;
This is exactly what I needed thank you so much! I thought of one other case that I couldn't figure out. How would I get a count (instead of a list) of all the customers that bought exactly two apples? I spent about 45 minutes hacking on the above but I couldn't get it. I'm thinking that would have to be a subselect? -- Scott Baker - Canby Telcom System Administrator - RHCE - 503.266.8253 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
