# For states (col) receiving exactly X number of items (row), sort the items by weight (col)

I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package.

I am able to get the States with the exact number of packages, but don't know how to then query only the packages with one of those states.

```SELECT State FROM testing GROUP BY State HAVING COUNT(State) = 7; ```

Can anyone point me in the right direction?

So from the data below, it would return the 10 packages from GA and MI, sorted by weight.

``````-------------------
| State | Weight  |
-------------------
| AL    | 29.745  |
| AZ    | 54.4081 |
| CA    | 3.79151 |
| CA    | 94.0579 |
| CT    | 68.6374 |
| CT    | 80.6644 |
| CT    | 55.8415 |
| FL    | 5.49145 |
| FL    | 76.6451 |
| FL    | 7.02167 |
| GA    | 84.3802 |
| GA    | 5.51498 |
| GA    | 12.9253 |
| GA    | 88.4529 |
| GA    | 38.8692 |
| MN    | 82.009  |
| MN    | 33.893  |
| MO    | 10.9912 |
| MS    | 41.8613 |
| MT    | 21.3695 |
| MI    | 18.9998 |
| MI    | 71.6003 |
| MI    | 72.0666 |
| MI    | 67.8444 |
| MI    | 72.2397 |
| ND    | 5.66484 |
| ND    | 80.8316 |
| NJ    | 81.8038 |
| NJ    | 14.91   |
| NJ    | 55.238  |

``````

I finally figured it out!

``````SELECT State, Weight FROM testing
WHERE State = ANY (
SELECT State FROM testing
GROUP BY State
HAVING COUNT(*) = 7
)
ORDER BY Weight DESC;
``````
• What about `WHERE State IN (SELECT....)`? – Vérace Mar 17 '19 at 12:15
• Be sure and mark your own answer as correct - that way others with the same problem might be able to get something from your efforts! No points though! :-) p.s. welcome to the forum! – Vérace Mar 17 '19 at 12:20

# Analytics

You really need to learn them.

``````with data as (
select "State", "Weight" -- State is a reserved word.
,count(*) over (partition by state) as "Packages/State"
from unnamed_table
)
select "State", "Weight"
from data
where "Packages/State" = 7
order by "State", "Weight"
``````