If 50% of item group selected (1 Viewer)

ErickaMeade · Sep 25, 2018

I'm not sure if this is possible or what it would be called to even search. I am trying to create a query that will pull data from one column of, for each customer, the value of 50% or more of a category is the same. The ones that are not the same as the other 50% will pull data from another column.

See sample below or attached image. Any help or direction would be appreciated

Source Data
Customer Category Item Discount ItemValue CategoryValue
ABC Co. Produce Apple 50% Green Red
ABC Co. Produce Pear 25% Green Red
ABC Co. Produce Artichoke 25% Green Red
ABC Co. Produce Pepper 25% Green Red
ABC Co. Produce Celery 15% Green Red
ABC Co. Produce Grapes 25% Green Red

Desired Result
Customer Discount Group Discount Expr1:Value
ABC Co. Produce 25% Red
ABC Co. Celery 15% Green
ABC Co. Apple 50% Green

The_Doc_Man · Sep 25, 2018

or what it would be called to even search

There are various ways to do this. A GROUP BY and COUNT combination would tell you how many elements were in each group. There are ways to make percentages in the columns rather than actual counts. Search for Aggregate Functions and GROUP BY clauses to see how to set this up.

To tie things together, you might make one query that DOESN'T have a GROUP BY and another that that DOES, so you can get totals and category counts, then JOIN the two queries by category to form the final query that you need for your category decisions. Layered queries are a form of "divide and conquer" strategy, which might do the trick for you.

Questions that weren't covered by your description include what you would do if your count came out that nothing was 50% (such as, maybe, you had three choices in the ratios of 40% + 30% + 30% =100%).

plog · Sep 25, 2018

the value of 50% or more of a category is the same

I think you might need to better define that:

Source:
Customer Category Item Discount ItemValue CategoryValue
ABC Co. Produce Apple 20% Green Red
ABC Co. Produce Pear 20% Green Red
ABC Co. Produce Artichoke 60% Green Red
ABC Co. Produce Pepper 20% Green Red
ABC Co. Produce Celery 60% Green Red
ABC Co. Produce Grapes 60% Green Red

Result
Customer Discount Group Discount Expr1:Value
ABC Co. Produce 20% Red
ABC Co. Produce 60% Red

Also, what will this query be used for? Is it going to be the end result, or will this query be used in other queries? If other queries what fileds will you be using to JOIN to other data sources in those queries?

CJ_London · Sep 25, 2018

If I've understood your logic, I can see a possible way of doing it, but it will involve multiple queries

Q1 - provides a count for each customer and category

Code:

SELECT Customer, Category, Count(category) as catCount
FROM srcData
GROUP BY Customer, Category

Q2 - provides a count of each discount by customer and category

Code:

SELECT Customer, Category, Discount, Count(discount) as dsCount
FROM srcData
GROUP BY Customer, Category, Discount

Q3 - determine percentage of discounts

Code:

SELECT Customer, Category, Discount, Q2.dsCount/Q1.catCount as PC
FROM Q1 INNER JOIN Q2 ON Q1.Customer=Q2.Customer AND Q1.Category=Q2.Category

Q4 - apply percentage rule to srcData

Code:

SELECT Customer, iif(PC>=50%, Category,item) as DiscountGroup, iif(PC>=50%,categoryvalue, itemvalue) as Expr1
FROM srcData INNER JOIN Q3 ON srcData.Customer=Q3.Customer AND srcData.Category=Category AND srcData.Discount=Q3.Discount

ErickaMeade · Sep 25, 2018

CJ_London - Thank you. That got me to exactly where I need to be.

ErickaMeade · Sep 25, 2018

plog - Yes, I will change to 51% and that will get past the perfectly halved categories. Thank you

CJ_London · Sep 25, 2018

or just >50% rather that >=50%

If 50% of item group selected (1 Viewer)

ErickaMeade

Registered User.

Attachments

The_Doc_Man

Immoderate Moderator

plog

Banishment Pending

CJ_London

Super Moderator

ErickaMeade

Registered User.

ErickaMeade

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread