Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Base: query for report with multiple sub queries

Hi, I need to create a monthly report of sales, I have three tables which have have joins from primary keys to foreign keys, I am using debian 9 (crunchbang) Libreoffice 5 and embedded ( struggling to connect Mariadb to Libreoffice).

I import all the sales data from a CSV sheet and divide the sheet into sales, customers details, with the pseudo as primary/foreign key for customers to make the sales sheet smaller. I have the payment sheet separate (paypal) and have again separated customers from payments in a similar fashion. for tax purposes I need to separate sales out in the following fashion; Home sales (France), EU Sales, and Export sales, to make things really complicated I sell antiques and hand made items, and I have postage costs, so I need to create a super query!:

postFR postEU postMONDE SalesMONDE SalesHandMadeFR SalesHandMadeEU SaleFRantiques SalesEUantique and Purchasevalueantiques

I have two tables for stock, one is hand made and the other is stock, but only stock is needed. With the fantastic help of ask.libreoffice I have been able to create various queries that gives me the information i need; below

main query SELECT DISTINCT "EBSales"."ID", "EBSales"."Payé le", "EBSales"."stock", "EBSales"."shades", "EBSales"."Quantité", CASE WHEN "EBSales"."shades" IS NULL THEN 0 ELSE "Quantité" * "Prix de vente" * "Rate" END AS "SHADES", CASE WHEN "EBSales"."stock" IS NULL THEN 0 ELSE "Quantité" * "Prix de vente" * "Rate" END AS "TTC", "EBSales"."Prix total", "paypal"."AvComm", "paypal"."Comm", "EBSales"."NDT", "paypal"."Remboursement", "EBSales"."Pays" FROM "EBSales", "paypal" WHERE "EBSales"."NDT" = "paypal"."NDT" AND MONTH( "EBSales"."Payé le" ) = :MOIS

This gives

image description

Here are two of the six queries I need to add,

  'SELECT "EBSales"."shades", "EBSales"."Quantité", "EBSales"."Payé le" AS "Date", "Quantité" * "Prix de vente" * "Rate" AS "FR SHADES €", "EBSales"."Pays" FROM "EBSales", "paypal" WHERE "EBSales"."NDT" = "paypal"."NDT" AND "EBSales"."shades" IS NOT NULL AND "EBSales"."Pays" = 'France métropolitaine' AND MONTH( "EBSales"."Payé le" ) = :MOIS'

and

'SELECT "EBSales"."Payé le" AS "Date", "Livraison et expédition" * "Rate" AS "Poste MONDE €", "EBSales"."Pays" FROM "EBSales", "paypal" WHERE "EBSales"."NDT" = "paypal"."NDT" AND "EBSales"."Pays" <> 'Royaume-Uni' AND "EBSales"."Pays" <> 'Malte' AND "EBSales"."Pays" <> 'Irlande (Eire)' AND "EBSales"."Pays" <> 'Grèce' AND "EBSales"."Pays" <> 'Allemagne' AND "EBSales"."Pays" <> 'Chypre' AND "EBSales"."Pays" <> 'Pays-Bas' AND "EBSales"."Pays" <> 'Danemark' AND "EBSales"."Pays" <> 'Suisse' AND "EBSales"."Pays" <> 'Italie' AND "EBSales"."Pays" <> 'Luxembourg' AND "EBSales"."Pays" <> 'France métropolitaine' AND MONTH( "EBSales"."Payé le" ) = :MOIS'

The others are very similar and Once some one can point me in the right direction I can add the others. I have spent the week searching including floppybunnies, wikis, nested queries, sub queries and more. Help will be gratefully received. Regards Paul