CODEWITHSUNDEEP
sql
Brett Hawkins
Brett Hawkins

Reputation: 11

Is a sub query needed here SQL

I am working on an assignment for school and I am slightly stuck. We are currently studying sub queries. But I am not sure that one is needed here. I am not looking for the full answer just a bump onto the right track of thought.

Here is the Question:

Write a SELECT statement that returns the name and discount percent of each Brand that has a unique discount percent. In other words, don’t include Brands that have the same discount percent as another Brand. Sort the results by the BrandName column.

I know that I need to return the BrandName, and BrandDiscountPercentage from the Brands table, but is a sub query needed here? Of so why can I not just use the DISTINCT keyword. I'm fairly new to SQL so I'm still learning but I'm just not sure why a sub query is needed here.

Again, not looking for the full answer, although if you must supply the full solution please at least explain a little so I can actually learn from it.

Upvotes: 0

Views: 65

Answers (3)

Valli
Valli

Reputation: 1450

You can use a subquery or self join to achieve this.

  1. You can select just the distinct discount percentage in a subquery and then retrieve the corresponding Brand name

    SELECT BRANDNAME, DIS_PERCENT FROM MyTable WHERE (1,DIS_PERCENT) in (SELECT count(), DIS_PERCENT FROM MyTable GROUP BY DIS_PERCENT HAVING count()=1)

  2. Or can be a corelated subquery

    SELECT BRANDNAME, DIS_PERCENT FROM MyTable m WHERE 1=(SELECT count(*) from Mytable where DIS_PERCENT =m.DIS_PERCENT)

  3. Or with a self join

    SELECT m.BRANDNAME, m.DIS_PERCENT FROM MyTable m, MyTABLE m1 WHERE m.rowid<>m1.rowid AND m.dis_percent<>m1.dis_percent

  4. Select Distinct name, Discount_percent from table This will NOT work here, Because it will give the distinct combination of Brand and percentage. Your requirement will not be met "don’t include Brands that have the same discount percent as another Brand."

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

DISTINCT is not the right approach, because that returns all the values in the data once -- even if they repeat.

But, you don't actually need a subquery for this. An aggregation query actually works:

select max(b.BrandName), BrandDiscountPercentage
from brands b
group by BrandDiscountPercentage
having min(b.BrandName) = max(b.BrandName);

This determines if there is more than one name for any given percentage. If the min() and max() are the same, then there is only one value. Max(b.BrandName) returns that unique value.

An equivalent having clause is:

having count(distinct b.BrandName) = 1

Upvotes: 0

Aducci
Aducci

Reputation: 26644

One way to accomplish this is to group by the BrandDiscountPercentage and then filter out the duplicate values. The easiest way to show this is by walking through the steps.

Select all query:

select 
   BrandName, 
   BrandDiscountPercentage 
from Brands

Results:

BrandName   BrandDiscountPercentage
A           1       
B           2
C           3
D           2

Group by BrandDiscountPercentage query:

select BrandDiscountPercentage, count(*)
from Brands
group by BrandDiscountPercentage

Results:

BrandDiscountPercentage     count(*)
1                           1
2                           2
3                           1

Filter out non unique BrandDiscountPercentage query:

select BrandDiscountPercentage, count(*)
from Brands
group by BrandDiscountPercentage
having count(*) = 1

Results:

BrandDiscountPercentage     count(*)
1                           1
3                           1

Include the BrandName by using an aggregate function query:

select min(BrandName), BrandDiscountPercentage
from brands
group by BrandDiscountPercentage
having count(*) = 1
order by min(BrandName)

Results:

BrandName   BrandDiscountPercentage
A           1       
C           3

Upvotes: 1

Related Questions