SQL-Grouping 1-4

1

Hello! I have a table of breweries that make various beers. I have grouped the number of beers brewed to each brewery, but now, I need to group by top 25%, then next 25%, etc.; grouping into 4 categories by percentage of beers brewed.

Here's what I have so far:

Select b.brewery_id, b.name as "Brewery Name", count(b.brewery_id) as "Number of Beers Brewed"
From DB2SLATE.beerdb_beers c
right Join DB2SLATE.beerdb_breweries b
on b.brewery_id = c.brewery_id
group by b.brewery_id, b.name order by "Number of Beers Brewed" desc;

2

please see NTILE function

3

Welcome, please provide sample data?

4

Thank you! I have solved 90% of my problem. Now, I need to sort by brewery_id within each "Rank". Here's my query and sample data:

Select b.brewery_id, b.name as "Brewery Name",
count(b.brewery_id) as "Number of Beers Brewed",
ntile(4) over (order by count(b.brewery_id)desc) as Rank
From DB2SLATE.beerdb_beers c
right Join DB2SLATE.beerdb_breweries b
on b.brewery_id = c.brewery_id group by b.brewery_id, b.name;

image
image2003×1184 145 KB

Rows 3, 4, and 5 should be in ASC by brewery ID.

I have tried partitioning the data, but that is not working either.

Thank you!

5

A simple ORDER BY should do it.

Select b.brewery_id, b.name as "Brewery Name",
count(b.brewery_id) as "Number of Beers Brewed",
ntile(4) over (order by count(b.brewery_id)desc) as Rank
From DB2SLATE.beerdb_beers c
right Join DB2SLATE.beerdb_breweries b
on b.brewery_id = c.brewery_id group by b.brewery_id, b.name
ORDER BY [Rank],[Number of Beers Brewed] DESC,b.brewery_id

;

6

Thank you, Jeff! It worked!

7

My next question is:

How can I use an alias in my WHERE clause to give me only Beers Ranked 10-1? Here's what I have so far:

select abv, beer_name as "Name of Beer",
dense_rank()over (order by (abv)desc) as "Rank_ABV"
From DB2SLATE.beerdb_beers
where abv >0
order by abv;

Results are below:

image
image437×649 26.3 KB

8

Use a CTE or other form of derived table and query that.