Top 10 Summaries in a Summary report

[David Ford]

We have a report that summarizes data by category, then subsummarizes by product sku and shows the total sold for that product.  We are trying to limit the report to 10 in each category.  If there are less than 10 then show them all of course.  The data itself is imported from an api in json form, then converted to a csv and converted to records.  There are approx 30000 records currently as the api sends us back line items from a pos system.  I thought about using the executesql function, but can't quite get it.  

Dave

[comment]

Here's a demo file I posted in another thread (which I cannot find). It shows how to produce a summary report showing only top N categories using a method known as Fast Summaries by Mikhail Edoshin. Your task is more complicated, seeing as you want to show top 10 subcategories in each category - but the principle is the same.

 

2 hours ago, David Ford said:

I thought about using the executesql function,

Interesting idea. I would start by finding out how to do this in "real" SQL - then see if it's feasible using Filemaker's limited SQL toolset.

TopNCategories.fmp12

[David Ford]

very close..except that I have Category > Subcategory.  If only category, it works well.  But some of my MainCategory's have 30 Sub Categories.  I  will look at the script to see if I can modify it.  As for the executesql, i was thinking about using a subquery like this or something like this and this by no means is accurate.

SELECT category) 

FROM sales

WHERE category 

IN (SELECT category, productname, Sum(quantity) FROM sales FETCH TOP 10 ROWS ONLY WHERE Sum(quantity) > 1)

Edited August 20, 2021 by David Ford

[comment]

Well, I said::

7 hours ago, comment said:

Your task is more complicated, seeing as you want to show top 10 subcategories in each category

and you responded with:

6 hours ago, David Ford said:

except that I have Category > Subcategory.  If only category, it works well.  But some of my MainCategory's have 30 Sub Categories. 

which makes this conversation rather bizarre. Or perhaps it isn't a conversation at all.

 

[bcooney]

21 hours ago, David Ford said:

The data itself is imported from an api in json form

Then work with the json. Have you explored https://jsonata.org ?

[David Ford]

Here is the sql that works, the results are below.  just have to figure out if this is convertable to filemaker. 

WITH Ranked
AS
(
   SELECT
     Category,ProductName,SKUCLEANED, SUM(quantity) AS QTY,
     ROW_NUMBER() OVER(PARTITION BY CATEGORY ORDER BY SKUCLEANED) AS RN
   FROM POSSALES GROUP BY CATEGORY, PRODUCTNAME, SKUCLEANED
)
SELECT Category,ProductName,skucleaned, QTY
FROM Ranked
WHERE RN <= 10 ORDER BY CATEGORY, QTY DESC

RESULTS:

CATEGORY	PRODUCTNAME	SKUCLEANED	QTY
Beverages	Tweed - Bakerstreet & Ginger - 1x355ml	320021_1X355ML___	206
Beverages	Deep Space - Deep Space - 1x222ml	320025_1X222ML___	159
Beverages	Mollo - 5.0 - 1x355ml	320039_1X355ML___	153
Beverages	XMG - Tropical Fruit - 1x236ml	320042_1X236ML___	147
Beverages	Little Victory - Sparkling Dark Cherry Beverage - 1x355ml	320033_1X355ML___	88
Beverages	Tweed - Houndstooth & Soda - 1x355ml	320001_1X355ML___	76
Beverages	House of Terpenes - Limonene & Sparkling Tonic - 1x355ml	320036_1X355ML___	59
Beverages	Houseplant - Grapefruit Sparkling Water - 1x355ml	320002_1X355ML___	44
Beverages	Little Victory - Sparkling Blood Orange Beverage - 1x355ml	320031_1X355ML___	26
Beverages	Ripple by The Green Organic Dutchman - Dissolvable Balanced Powder - 4x0.45g	320017_4X0.45G___	5
Capsules	Redecan - Gems 5:0 - 15 caps	101071_15CAPS___	51
Capsules	Redecan - Gems 5:5 - 15 caps	101421_15CAPS___	37
Capsules	Redecan - CBD Gems - 15 caps	101480_15CAPS___	17
Capsules	Tweed - CBD Softgels - 15 caps	101096_15CAPS___	8
Capsules	Tweed - Penelope Softgels 10 mg - 15 caps	100029_15CAPS___	4
Capsules	Tweed - Penelope Softgels 2.5 mg - 15 caps	100273_15CAPS___	3
Capsules	Redecan - CBD Gems +	101861_15CAPS___	2
Dried Flower	RIFF - Subway Scientist - 3.5g	100079_3.5G___	118
Dried Flower	Solei - Balance - 1g	100063_1G___	54
Dried Flower	LBS - Sunset - 3.5g	100013_3.5G___	54
Dried Flower	LBS - Sunset - 1g	100013_1G___	52
Dried Flower	Tweed - Hindu Kush - 1g	100019_1G___	51
Dried Flower	Tweed - C-LAND - 1g	100023_1G___	42
Dried Flower	Good Supply - Jean Guy - 15g	100080_15G___	15
Dried Flower	LBS - Sunset - 7g	100013_7G___	14
Dried Flower	Solei - Balance - 3.5g	100063_3.5G___	7
Dried Flower	Tweed - Afghan Kush - 3.5g	100031_3.5G___	3
Edibles	Bhang - THC Milk Chocolate Bar - 1x10g	330028_1X10G___	208
Edibles	Bhang - THC Dark Chocolate Bar - 1x10g	330029_1X10G___	131
Edibles	Foray - Peach Mango Soft Chews (2-Pieces) - 2x5g	330007_2X5G___	85
Edibles	Foray - Raspberry Vanilla Soft Chews (2-Pieces) - 2x5g	330010_2X5G___	60
Edibles	Chowie Wowie - Balance Solid Milk Chocolate - 1x16g	330026_1X16G___	58
Edibles	Affirma - Watermelon Soft Chew - 1x3.2g	330050_1X3.2G___	49
Edibles	Foray - Salted Caramel Chocolate Square - 1x10g	330003_1X10G___	25
Edibles	Aurora Drift - Peach Serene CBD Soft Chews - 5x4.3g	330022_5X4.3G___	25
Edibles	Chowie Wowie - THC Solid Milk Chocolate - 1x16g	330024_1X16G___	24
Edibles	Affirma - Sour Cherry Soft Chew - 1x3.2g	330049_1X3.2G___	16
Extracts	Original Stash - OS.Hash10 - 2g	310007_2G___	104
Extracts	San Rafael '71 - OG Chemdog Live Resin - 1g	310021_1G___	65
Extracts	Hiway - Hash - 2g	310022_2G___	56
Extracts	Canna Farms - BC Bubble Hash - 1g	310006_1G___	44
Extracts	Greybeard - KISH Terp Slush Live Resin - 1g	310018_1G___	37
Extracts	Greybeard - SLK Terp Slush Live Resin - 1g	310019_1G___	33
Extracts	48North - Traditional Pressed Hashish - 1g	310014_1G___	29
Extracts	Fireside X - Glueberry Shatter - 1g	310013_1G___	18
Extracts	7ACRES - Sensi Star Flower Rosin - 1g	310023_1G___	13
Extracts	Fireside X - Wappa Shatter - 1g	310001_1G___	10
Oils	Redecan - Reign Drops 30:0 - 30ml	100490_30ML___	84
Oils	Redecan - CBD Reign Drops - 30ml	101481_30ML___	60
Oils	Redecan - Reign Drops 15:15 - 30ml	101482_30ML___	31
Oils	Solei - Free Oil - 30ml	100420_30ML___	19
Oils	MediPharm Labs - CBD 50 Plus Formula Oil - 20ml	101885_20ML___	12
Oils	Twd - Indica Oral Spray - 20ml	101311_20ML___	4
Oils	Twd - Sativa Oral Spray - 20ml	101312_20ML___	2
Oils	Pure Sunfarms - Pure Sun CBD Oil 1:10 - 30ml	101595_30ML___	2
Oils	Tweed - Penelope Oral Spray - 20ml	101315_20ML___	1
Oils	Pure Sunfarms - Pure Sun CBD Oil 1:30 - 30ml	101596_30ML___	1
Pre-Rolls	Good Supply - Jean Guy Pre-Roll - 1x1g	100074_1X1G___	248
Pre-Rolls	Good Supply - Jean Guy Pre-Roll - 7x0.5g	100074_7X0.5G___	206
Pre-Rolls	RIFF - Subway Scientist Pre-Roll - 1g	100073_1G___	179
Pre-Rolls	RIFF - Subway Scientist Pre-Roll - 7x0.5g	100073_7X0.5G___	101
Pre-Rolls	Trailblazer - Glow Stix - 1x0.5g	100752_1X0.5G___	59
Pre-Rolls	Tweed - Hindu Kush Pre-Roll - 5x0.5g	100412_5X0.5G___	42
Pre-Rolls	RIFF - DT81 Pre-Roll - 1x1g	100997_1X1G___	40
Pre-Rolls	Trailblazer - Flicker Stix - 1x0.5g	100750_1X0.5G___	24
Pre-Rolls	RIFF - DT81 Pre-Roll - 7x0.5g	100997_7X0.5G___	19
Pre-Rolls	Trailblazer - Spark Stix - 1x0.5g	100751_1X0.5G___	14
Seeds	34 Street Seed Co. - Pineapple Express Seeds - 5 Pack	102003_5 PACK___	8
Seeds	Humboldt Seed Company - Magic Melon (Autoflower) Seeds - 5 Pack	102000_5 PACK___	7
Seeds	34 Street Seed Co. - Bubba Kush Seeds - 5 Pack	102004_5 PACK___	6
Seeds	34 Street Seed Co. - Triple Scoop Seeds - 5 Pack	102005_5 PACK___	5
Seeds	34 Street Seed Co. - Pink Lemonade Seeds - 4 Pack	101390_4PACK___	3
Seeds	Tweed - Bakerstreet Seeds - 4 Pack	100968_4PACK___	2
Seeds	34 Street Seed Co. - AK-47 (AUTO) Seeds - 5 Pack	101832_5 PACK___	2
Seeds	34 Street Seed Co. - Mango CBD 1:1 Seeds - 5 Pack	101831_5 PACK___	1
Seeds	CRG Pharma - Charlotte's Diesel Seeds - 4 Pack	102006_4 PACK___	1
Seeds	CRG Pharma - Wedding Cake Seeds - 4 Pack	102007_4 PACK___	1
Topicals	Solei - Balance - 75g	340021_75G___	16
Topicals	Eve & Co. - The Boss Bath Bomb - 140g	340016_140G___	12
Topicals	Nuance - CBD Eucalyptus Bath Bomb - 270g	340040_270G___	7
Topicals	Latitude by 48North - Sex Pot Intimacy Oil - 25g	340019_25G___	2
Topicals	Solei - Free Unscented - 75g	340023_75G___	2
Topicals	Tidal - French Vanilla Lip Balm - 5g	340038_5G___	1
Topicals	Proofly - Water-Based Unisex Lubricant - 25g	340033_25G___	1
Vapes	Good Supply - Pineapple Express 510 Thread Cartridge - 1g	300024_1G___	70
Vapes	Kolab Project - Strawberry Ice Sativa 510 Thread Cartridge - 0.5g	300038_0.5G___	67
Vapes	Redecan - Trainwreck Redee 510 Thread Cartridge - 0.45g	300081_0.45G___	41
Vapes	Kolab Project - Blackberry Cream Indica 510 Thread Cartridge - 0.5g	300037_0.5G___	39
Vapes	Good Supply - Blue Dream 510 Thread Cartridge - 1g	300023_1G___	35
Vapes	Good Supply - Pineapple Express 510 Thread Cartridge - 0.5g	300024_0.5G___	32
Vapes	Good Supply - Blue Dream 510 Thread Cartridge - 0.5g	300023_0.5G___	29
Vapes	CANACA - THC Distillate 510 Thread Cartridge - 0.5g	300110_0.5G___	13
Vapes	Redecan - Zktlz Redee 510 Thread Cartridge - 0.45g	300121_0.45G___	13
Vapes	Foray - Mango Haze Balanced 510 Thread Cartridge - 0.5g	300036_0.5G___	8