jeudi 3 décembre 2015

Queries to group items with variants

I have this data:

"progress" "description" "quantity"
"1" "crodino"   "1"
"2" "bitter"    "1"
"3" "+ghiaccio" "1"
"4" "crodino"   "2"
"5" "bitter"    "1"
"6" "crodino"   "1"
"7" "bitter"    "1"
"8" "+limone"   "1"
"9" "-oliva"    "1"
"10" "bitter"   "1"
"1" "bitter"    "2"

The items without "+" or "-" in the first char are normal products, but the items with "+" or "-" are the variants (ex: +limone and - oliva are the variants of bitter, which have the progress number 7)

I need this result with a query:

"progress" "description" "quantity"
"1" "bitter" "4"
"2" "bitter" "1"
"3" "+limone" "1"
"4" "- oliva" "1"
"5" "bitter" "1"
"6" "+ghiaccio" "1"
"7" "crodino" "4"

This:

  • First get the products with the variants in the next progress number and display it
  • Alphabetically order the main product, then the variants in it.
  • Group and sum the quantity in the other products

What is the query for doing it?

My db is sqlite.

Aucun commentaire:

Enregistrer un commentaire