SQL count at least

Question

I need to find the makers producing at least three distinct models of PCs. Result set: maker, number of models.

My tables are Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price)

I have done this for now

SELECT Product.maker, PC.model 
FROM PC, Product 
WHERE Product.model=PC.model

what engine? MySQL, SQL Server, ...? – gbn Jan 25 '12 at 15:28 — gbn, Jan 25 '12 at 15:28

score 5 · Answer 1 · answered Jun 04 '13 at 12:37

PC table contains only the computers having been produced and it may be missing some existing models not yet produced:

The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price.

All the models are contained in the Product table.

It turns out that Product table has the flag what type of product it is: type.

So, the query doesn't really even need a join:

select maker, count(model)
from Product
where type = 'PC'
group by maker
having count(model) >= 3

score 4 · Answer 2 · answered Nov 29 '16 at 22:15

4

I had the same problem: So try this query, hope you are looking for this one:

SELECT maker, COUNT(DISTINCT model)
FROM product
WHERE type = 'PC'
GROUP BY maker
HAVING COUNT(DISTINCT model) > 2

answered Nov 29 '16 at 22:15

besartm

558
1
7
14

score 2 · Answer 3 · answered Jun 28 '13 at 09:01

2

This will work

SELECT P.maker, count(P.model) as Count_Model
from product P
where p.type = 'pc'
group by maker
having count(p.model) >=3

answered Jun 28 '13 at 09:01

zviad

586
7
18

score 2 · Answer 4 · answered Jan 25 '12 at 15:24

2

You may try this query:

select Product.maker, count(distinct Product.model)
from PC inner join Product on PC.model = Product.model
group by Product.maker
having count(distinct Product.model) >= 3

answered Jan 25 '12 at 15:24

Timofey

2,478
3
37
53

2

+1 for correct use of standard SQL aggregate I'd like to add another +1 for explicit join... – gbn Jan 25 '12 at 15:28
@Darko that means that you don't have such producers that produce at least 3 distinct models of PC :) – Timofey Jan 25 '12 at 16:00
I have one produces with count of 3, why am i not getting that producer? – DPE Jan 25 '12 at 16:05
@Darko may you post the data for this producer here? – Timofey Jan 25 '12 at 16:08
@Tim here is the data from the Product table maker model type A 1232 PC A 1233 PC A 1276 Printer A 1298 Laptop A 1401 Printer A 1408 Printer A 1752 Laptop B 1121 PC B 1750 Laptop C 1321 Laptop D 1288 Printer D 1433 Printer E 1260 PC E 1434 Printer E 2112 PC E 2113 PC – DPE Jan 25 '12 at 16:17
@Darko can you post also the content of PC table? I suppose that there is no foreign key constraint therefore *E 1260 PC*, *E 2112 PC* and *E 2113 PC* are not the result of join between Product and PC table. – Timofey Jan 25 '12 at 16:34
@Tim this is the data from the PC table code model speed ram hd cd price 1 1232 500 64 5.0 12x 600.0000 10 1260 500 32 10.0 12x 350.0000 11 1233 900 128 40.0 40x 980.0000 12 1233 800 128 20.0 50x 970.0000 2 1121 750 128 14.0 40x 850.0000 3 1233 500 64 5.0 12x 600.0000 4 1121 600 128 14.0 40x 850.0000 5 1121 600 128 8.0 40x 850.0000 6 1233 750 128 20.0 50x 950.0000 7 1232 500 32 10.0 12x 400.0000 8 1232 450 64 8.0 24x 350.0000 9 1232 450 32 10.0 24x 350.0000 – DPE Jan 25 '12 at 16:37
@Darko exactly you don't have entries for model **2112** and **2113**, so you have to insert them in the PC table – Timofey Jan 25 '12 at 16:41
@Darko I would suggest to make a foreign key between PC table and Product table. – Timofey Jan 25 '12 at 16:44

score 1 · Answer 5 · answered Jan 25 '12 at 15:23

1

Try GROUP BY Product.maker HAVING COUNT(PC.model) >= 3.

answered Jan 25 '12 at 15:23

Borealid

95,191
9
106
122

score 0 · Answer 6 · edited Dec 06 '13 at 20:45

0

This will work:

SELECT maker,COUNT(model) AS Count_Models FROM Product 
    WHERE type = 'PC' 
    GROUP BY maker 
    HAVING COUNT(DISTINCT model) >= 3

edited Dec 06 '13 at 20:45

Niall C.

10,878
7
69
61

answered Dec 06 '13 at 20:27

XY_88

1

score 0 · Answer 7 · edited May 02 '19 at 11:01

0

I suggest following query:

select distinct maker,COUNT(model) AS Count_model from Product where type='PC' GROUP BY maker HAVING COUNT(model)>=3

edited May 02 '19 at 11:01

kk.

3,747
12
36
67

answered May 02 '19 at 10:51

uttam rawat

9
2

SQL count at least

7 Answers7