SQL Question (mysql)?

Question

Say I had a table with two columns. One with a customers number, and one with the number of products bought per visit. How would I find the customer number of customers that bought, say, 3 or 4 products but not 1, 2, 5 6, 7....etc products. If you write an sql i would be grateful or even just the clause word would be helpful.

Answer 1

select

from a join b using ()

where

group by

having count(*) in (3, 4) <---- most important part

Answer 2

Since your question only mentions one table, there is no need for a join. You simply select the customer numbers where the number of items purchased [ItemsPurchased] is 3 or 4.



SELECT [customernumber], [ItemsPurchased]

FROM CustomerTable

WHERE ItemsPurchased=3 Or ItemsPurchased=4;

Answer 3

SQL Server, MySQL and Oracle SQL code is usually about 99% identical for a given query. The main differences are in SQL extensions (for example, where one might use TOP, another uses LIMIT) and conversion functions (for example, datetime <-> string). I'm not sure what you mean by "Which module would be the perfect platform - SQL server or DBA?" There is no basis for comparison. One is a RDBMS, the other is a profession.

Answer 4

The other answers are incorrect because they do not add up all the purchases by the same customer... here is one that does:



SELECT customer_number FROM

(SELECT customer_number, sum(number_products) as TotalPurchased FROM

tblCustomers group by customer_number) as sql1

where (TotalPurchased=3 or TotalPurchased=4)

Answer 5

select Customer.Customerno from customer, products

where Customer.CustomerNo = products.customerNo

group by products.productcount

having count(products.productcount) = 3 or 4

Answer 6

It is unclear from your question whether you want to sum the number of products bought in all visits by each customer and test that, or test against the number bought in each individual trip. But judging by the clarification, I think you want to look at individual trips, and you only want the customers who bought 3 or 4 items every time. Here are some queries that would do that.



select custno

from visits

where itemcount in (3, 4) and

custno not in

(select custno from visits where itemcount not in (3,4));



or this:



select custno

from visits a

where itemcount in (3, 4) and

not exist

(select custno from visits b

where b.custno=a.custno and itemcount not in (3,4));



And actually, the first test is redundant, so you could do this:



select distinct custno

from visits

where custno not in

(select custno from visits where itemcount not in (3,4));



Or it might be more efficient this way:



create temporary table badcounts

(custno int primary key)

select distinct custno from visits where itemcount not in (3,4);



select distinct custno

from visits v left join badcounts b using (custno)

where b.custno is null;



or you could try this:



select distinct custno

from visits v

left join (select custno from visits where itemcount not in (3,4)) b

using (custno)

where b.custno is null;



If you are always interested in a range, you could try this:



select custno from visits

group by custno

having min(itemcount) >= 3 and max(itemcount) <= 4;



This last one may be faster if you have an index on (custno, itemcount).

Answer 7

SELECT custNum FROM yourtable WHERE numOfProducts BETWEEN 3 AND 4;



The numbers you provide (3 and 4 in this example) is inclusive.

Answer 8

well i knw only SQL 99



the query should be somethin like this:



select custnumb from cust where (numpro=3 or numpro=4)