0

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  |

0

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 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 at 12:20
0

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.