Re: [sqlite] sqlite-users Digest, Vol 38, Issue 4

[Igor Tandetnik]

On 2/4/2011 11:14 AM, Scott Baker wrote:
> On 02/04/2011 04:00 AM, sqlite-users-requ...@sqlite.org wrote:
>> On 2/3/2011 12:10 PM, Scott Baker wrote:
   #3) Query for customers who bought exactly 2 apples?
>> select CustomerID from Customers
>> group by CustomerID
>> having sum(Type = 'Apple') = 2;
>
> 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?

select count(*) from (
   select CustomerID from Customers
   group by CustomerID
   having sum(Type = 'Apple') = 2;
)

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 38, Issue 4

[Scott Baker]

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