CODEWITHSUNDEEP
sqloracle-database
user2987377
user2987377

Reputation: 91

querying using count

I have the following database:

Stocks (StockNo, Storecode, Description, Quantity, Units, Reorder, Price, SuppCode)

This is a sample of the data contained within it:

STOCKNO STOREC DESCRIPTION             QUANTITY UNITS  REORDER   PRICE SUPPCO
------- ------ ----------------------- -------- ------ ------- ------- ------
    126 LEG    Sealing wax                    9 Box          5    7.99 S5
    127 LEG    Red binding ribbon            13 Roll        10    6.47 S5
    128 LEG    A3 cream notary paper         21 Ream        10    7.85 S5
    129 LEG    Coloured ink                  22 Bottle      10    3.48 S4

I need make a query to display the suppliers (suppcode) that supply at least 3 stores.

This I what I have so far:

select suppcode, count(distinct storecode) as StoresSupplied
from stocks
group by suppcode
having count(*) > 3

This produces the following results

SUPPCO STORESSUPPLIED
------ --------------
S3                  4
S4                  3
S2                  3
S1                  1

Check the whole table the results for S3, S4 and S2 are correct but I cannot find any reason why S1 is there. S1 appears on the table supplying 8 different items but to only 1 individual storecode.

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would suggest you just use the alias from the select:

select suppcode, count(distinct storecode) as StoresSupplied
from stocks
group by suppcode
having StoresSupplied > 3;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Count(*) is going to include Null rows where as count(distinct storecode) will not. Try changing like this

select suppcode, count(distinct storecode) as StoresSupplied
from stocks
group by suppcode
having count(distinct storecode) > 3

Upvotes: 1

Related Questions