CODEWITHSUNDEEP
sqlderby
linlai
linlai

Reputation: 21

Using Derby SQL to calculate value for histogram

I have a table with various SKU in totes.

The table is totecontents with below columns:

Each Tote can contain a maximum of 6 SKUs. (programmatically constrained)

select toteid, count(*) as qtypertote

from totecontents 

group by toteid;

gives me a list of totes with the number of skus in each.

I now want to get to a table with following result

SkuCount Occurences where each row would have the ordinal value (1 through 6 ) and then the number of occurences of that value.

My efforts included the following approach

select count(*) 
from 
      ( select toteid, count(*) as qtypertote 
        from totecontents 
        group by toteid)
group by qtypertote;

Upvotes: 0

Views: 41

Answers (1)

linlai
linlai

Reputation: 21

Stung by the comments I performed more research. This works:

SELECT CountOfskus, COUNT(1) groupedCount

FROM

( SELECT COUNT(*) as countofskus, toteid

  FROM totecontents

  Group By toteid 

) MyTable

GROUP BY countofskus;

Upvotes: 1

Related Questions