[Solved] Help with query re spread of sizes

[cartoonjazz]

Can someone offer some guidance for a newb not sure how to do this
I have a table that contains a list of skus with size after a dash, quantities and sizes

I need the query to search within the quantity/size per each grouping of same skus before the dash and
-if there 1 or greater in quantity in 2 out of the following sizes: 71, 76, 81 & 86 and not in sizes 91, 97, 102, 107 & 112 another column should say "women"

-if there 1 or greater in quantity in 2 out of the following sizes: 91, 97, 102, 107 & 112 and not in sizes 71, 76, 81 & 86 the other column should say "men"

-if there is 1 or greater in quantity in 1 of the sizes in both groups then the column should say "both"
the result should look like this

Let me know if you have any questions

[eremmel]

In Base you can define queries and those query-names you can use in another query.
I assume that the size value for your sku without a dash is null. You need to add to the queries below some double quotes around the names etc, below it is just a sketch...
Call the following query "SkuGroups"

Code: Select all

SELECT sku AS skugroup FROM table WHERE size IS NULL

Call the following query "SkuWithQuantity"

Code: Select all

SELECT SkuGroups.skugroup, table.sku, table.quantity, table.size
  , CASE WHEN table.size in ( 71,76,81,86) THEN 1 ELSE 0 END as men
  , CASE WHEN table.size in ( 71,76,81,86) THEN 0 ELSE 1 END as woman
FROM SkuGroups INNER JOIN table ON SkuGroups.skugroup = SUBSTR(table.sku,1,6)
WHERE table.quantity > 0

Call the following query "SkuGender"

Code: Select all

SELECT skugroup 
CASE WHEN SUM(men) >=  1 and SUM(women) >= 1 THEN 'both'
  WHEN SUM(men) >= 2 THEN 'men'
  WHEN SUM(woman) >= 2 THEN 'women'
  ELSE 'alien' END as "gender"
FROM SkuWithQuantity
GROUP BY skugroup 

Call the following query "FinalResult"

Code: Select all

SELECT SkuWithQuantity.sku, SkuKind.gender
FROM SkuGender INNER JOIN SkuWithQuantity ON SkuGender.skugroup = SkuWithQuantity.skugroup
WHERE SkuGender.gender <> 'alien'
ORDER BY SkuWithQuantity.sku 

[cartoonjazz]

Thanks, I tried it but Im getting errors in all queries except "SkuGroups"

"SkuWithQuantity" has an error "ùnexpected token: men" here is the adjusted query with quotes
SELECT "SkuGroups"."skugroup", "table"."sku", "table"."quantity", "table"."size"
, CASE WHEN "table"."size" in ( 71,76,81,86) THEN 1 ELSE 0 END as 'men'
, CASE WHEN "table"."size" in ( 71,76,81,86) THEN 0 ELSE 1 END as 'woman'
FROM "SkuGroups" INNER JOIN "table" ON "SkuGroups"."skugroup" = SUBSTR("table"."sku",1,6)
WHERE "table"."quantity" > 0

then all the other queries rely on this query so they dont work i have put them in. If you can confirm the quotes are correct.
"SkuGender"
SELECT "skugroup"
CASE WHEN SUM('men') >= 1 and SUM('women') >= 1 THEN 'both'
WHEN SUM('men') >= 2 THEN 'men'
WHEN SUM('woman') >= 2 THEN 'women'
ELSE 'alien' END as "gender"
FROM "SkuWithQuantity"
GROUP BY "skugroup"

"result"
SELECT "SkuWithQuantity"."sku", "SkuKind"."gender"
FROM "SkuGender" INNER JOIN "SkuWithQuantity" ON "SkuGender"."skugroup" = "SkuWithQuantity"."skugroup"
WHERE "SkuGender"."gender" <> 'alien'
ORDER BY "SkuWithQuantity"."sku"

[eremmel]

Try to use' Direct SQL' , mention the error details, Include a demo Base document with the queries.

Edit: 1: A note in your queries: Alias names (rename a column name or table name) can not be placed between single quotes, but can be placed between double quotes. Single quotes are pure reserved for string literals. So some men/women have to be written as "men" / "women" and others as 'men' / ' women'. 2: I also mixed up "women" and "woman" make sure all read as "women"

[UnklDonald418]

I've been following this thread and have found that the query SqlGender won't run on an embedded database. A simple CASE WHEN ELSE will work but CASE WHEN WHEN ELSE generates an error. The SqlGender query does work in a split database, in fact the I got it all working after one more correction to the query FinalResult. "SkuKind"."gender" should be "SkuGender"."gender"

To convert to a split database see:
viewtopic.php?f=83&t=65980

[eremmel]

@UnkDonald418, A "CASE WHEN WHEN ELSE END" is supported but not with the combination of SUM(). After the feedback of cartoonjazz, I tested it quickly and need to transform query "SqlGender" into

Code: Select all

SELECT "SWQ"."skugroup", "men", "women"
     , CASE WHEN "SWQ"."men" >= 1 AND "SWQ"."women" >= 1 THEN 'both' 
         WHEN "SWQ"."men" >= 2 THEN 'men' 
         WHEN "SWQ"."women" >= 2 THEN 'women' 
         ELSE 'alien' END AS "gender" 
FROM ( SELECT "skugroup", SUM( "men" ) "men", SUM( "women" ) "women" 
              FROM "SkuWithQuantity" 
              GROUP BY "skugroup" ) "SWQ"

[cartoonjazz]

Here are all the adjusted queries now working thanks very much to eremmel
"SkuWithQuantity"
SELECT "SkuGroups"."skugroup", "table"."sku", "table"."quantity", "table"."size", CASE WHEN "table"."size" IN ( 71, 76, 81, 86 ) THEN 1 ELSE 0 END AS "men", CASE WHEN "table"."size" IN ( 71, 76, 81, 86 ) THEN 0 ELSE 1 END AS "women" FROM "SkuGroups" INNER JOIN "table" ON "SkuGroups"."skugroup" = SUBSTR( "table"."sku", 1, 6 ) WHERE "table"."quantity" > 0

"SkuGender"
SELECT "SWQ"."skugroup", "men", "women", CASE WHEN "SWQ"."men" >= 1 AND "SWQ"."women" >= 1 THEN 'both' WHEN "SWQ"."men" >= 2 THEN 'men' WHEN "SWQ"."women" >= 2 THEN 'women' ELSE 'alien' END AS "gender" FROM ( SELECT "skugroup", SUM( "men" ) "men", SUM( "women" ) "women" FROM "SkuWithQuantity" GROUP BY "skugroup" ) "SWQ"

"result"
SELECT "SkuWithQuantity"."sku", "SkuGender"."gender" FROM "SkuGender" INNER JOIN "SkuWithQuantity" ON "SkuGender"."skugroup" = "SkuWithQuantity"."skugroup" WHERE "SkuGender"."gender" <> 'alien' ORDER BY "SkuWithQuantity"."sku"