Ask Your Question

pascale's profile - activity

2018-04-30 23:41:48 +0100 received badge  Famous Question (source)
2018-04-30 23:38:03 +0100 received badge  Notable Question (source)
2018-02-12 21:15:51 +0100 received badge  Famous Question (source)
2017-12-07 09:30:12 +0100 received badge  Notable Question (source)
2017-11-23 17:46:48 +0100 received badge  Popular Question (source)
2017-11-23 15:26:33 +0100 received badge  Notable Question (source)
2017-11-23 15:02:22 +0100 received badge  Notable Question (source)
2017-11-23 12:40:07 +0100 edited question Every derived table must have its own alias error

Every derived table must have its own alias error Hi I have just managed to set up Mariadb and created a new table and m

2017-11-23 12:31:55 +0100 edited question Every derived table must have its own alias error

Every derived table must have its own alias error Hi I have just managed to set up Mariadb and created a new table and m

2017-11-23 12:21:28 +0100 edited question Every derived table must have its own alias error

Every derived table must have its own alias error Hi I have just managed to set up Mariadb and created a new table and m

2017-11-23 09:20:04 +0100 asked a question Every derived table must have its own alias error

Every derived table must have its own alias error Hi I have just managed to set up Mariadb and created a new table and m

2017-10-21 23:54:27 +0100 received badge  Popular Question (source)
2017-10-21 21:32:36 +0100 marked best answer 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

2017-10-21 21:32:14 +0100 answered a question Base: query for report with multiple sub queries

Well Thank you again Mr @Ratslinger, for your help and advise, and mainly not just giving me the answer when you could h

2017-10-20 23:34:50 +0100 commented question Base: query for report with multiple sub queries

Hi, I have looked at your link and have tried as far as possible to follow this line of normalization, before starting t

2017-10-20 23:19:53 +0100 commented answer Base: query for report with multiple sub queries

Hi @Ratslinger thank you for coming back so quickly, I will take a look at the link tomorrow (midnight here) and get bac

2017-10-20 22:11:29 +0100 asked a question Base: query for report with multiple sub queries

Base: query for report with multiple sub queries Hi, I need to create a monthly report of sales, I have three tables whi

2017-10-16 20:48:19 +0100 commented answer Query problem with "AND" "OR" "LIKE"

Thanks again for your help. I looked at link text which explains using outside DB. Regards paul

2017-10-16 11:49:59 +0100 received badge  Popular Question (source)
2017-10-16 10:51:38 +0100 commented answer Query problem with "AND" "OR" "LIKE"

Hi Mr @Ratslinger, yes you are right again you SQL statement is Just what I need, it does exactly what I want, AGAIN th

2017-10-16 10:43:56 +0100 marked best answer Query problem with "AND" "OR" "LIKE"

Hi, I have created a query in SQL that gives me the answer I want for one element, but i would like it to give me the correct answer for several elementSELECT "GEN", "debit", "credit", "debit" - "credit" "Solde" FROM ( SELECT ( "ID" ) "GEN" FROM "General" WHERE "ID" = '445663' ), ( SELECT SUM( "Montant" ) "debit" FROM "MONGENDEB" WHERE MONTH( "Date" ) = :MOIS AND "Debit" = '445663' ), ( SELECT SUM( "Montant" ) "credit" FROM "MONGENCRED" WHERE MONTH( "Date" ) = :MOIS AND "Credit" = '445663' )I would like to have the reply for 445663, 445662, 445000 and several others, I have tried "AND", "OR" and "LIKE" with varying result from "Syntax error" to "Not in this Table". Any help or advise will be greatly appreciated Kind regards Paul

2017-10-15 23:31:31 +0100 commented answer Query problem with "AND" "OR" "LIKE"

(continued) I will get back to you tomorrow morning. Goodnight from Bordeaux. Regards Paul

2017-10-15 23:30:03 +0100 commented answer Query problem with "AND" "OR" "LIKE"

HI again, yes it is similar !! I have tried your advise but it gives me the two lines with the same total (the correct t

2017-10-15 22:08:37 +0100 asked a question Query problem with "AND" "OR" "LIKE"

Query problem with "AND" "OR" "LIKE" Hi, I have created a query in SQL that gives me the answer I want for one element,

2017-10-15 14:43:50 +0100 received badge  Popular Question (source)
2017-10-08 11:57:12 +0100 commented answer base trouble with critirion in a query

Ooooooo mr @ratslinger you are magic!!!! I knew it would be something simple, (but still far to complicated for me!) Tha

2017-10-08 11:55:03 +0100 marked best answer base trouble with critirion in a query

Hi, I am having trouble with the results of a query, here is the sql;

SELECT "EBSales"."shades", "EBSales"."Prix de vente" AS "EU TTC Devise", "EBSales"."NDT", "EBSales"."Quantité", "EBSales"."Payé le" AS "Date", "paypal"."Rate", "Quantité" * "Prix de vente" * "Rate" AS "EU TTC €", "EBSales"."Pays" FROM "EBSales", "paypal" WHERE "EBSales"."NDT" = "paypal"."NDT" AND ( "EBSales"."shades" IS NOT NULL AND "EBSales"."Pays" = 'Royaume-Uni' OR "EBSales"."Pays" = 'Malte' OR "EBSales"."Pays" = 'Irlande (Eire)' OR "EBSales"."Pays" = 'Grèce' OR "EBSales"."Pays" = 'Allemagne' OR "EBSales"."Pays" = 'Chypre' OR "EBSales"."Pays" = 'Pays-Bas' OR "EBSales"."Pays" = 'Danemark' OR "EBSales"."Pays" = 'Suisse' OR "EBSales"."Pays" = 'Italie' OR "EBSales"."Pays" = 'Luxembourg' )

And here is a screen shot image description

In the first column "Shades" I only want to see rows which have a LS number ie "IS NOT EMPTY" but for certain countries in my list that are part of the eu appear!.

I have changed the "OR" for "AND" which left a blank table, and I have gone into "query properties" and changed the distinct values to yes but this only added more rows with the first column empty. Any Ideas??? kind regards Paul

NB, I have another query with only one country that work great

2017-10-07 22:30:49 +0100 edited question base trouble with critirion in a query

base trouble with critirion in a query Hi, I am having trouble with the results of a query, here is the sql; SELECT "EB

2017-10-07 22:30:30 +0100 edited question base trouble with critirion in a query

base trouble with critirion in a query Hi, I am having trouble with the results of a query, here is the sql; SELECT "EB

2017-10-07 22:29:00 +0100 asked a question base trouble with critirion in a query

base trouble with critirion in a query Hi, I am having trouble with the results of a query, here is the sql; SELECT "EB

2017-09-30 20:21:24 +0100 commented answer combining three parametering queries and then combining their totals

@Ratslinger The updated SQL works great, Thank you. There is never a need to apologize when you are helping somebody. I

2017-09-30 16:12:44 +0100 received badge  Popular Question (source)
2017-09-30 13:45:06 +0100 commented answer combining three parametering queries and then combining their totals

Hi @ratslinger, I have looked back at your previous comments and see that you feel the underling problem is the table "P

2017-09-30 13:45:06 +0100 received badge  Commentator
2017-09-30 09:22:10 +0100 edited question combining three parametering queries and then combining their totals

combining three parametering queries and then combining their totals Hi, I have three queries coming from the same table

2017-09-30 09:15:19 +0100 commented answer combining three parametering queries and then combining their totals

Hi @ratslinger, Thank you for your interest, "I feel obligated to again state this could be much better with some carefu

2017-09-30 08:25:11 +0100 marked best answer combining three parametering queries and then combining their totals

Hi, I have three queries coming from the same table

SELECT "ID", "Date", "Dévise" AS "EUR", "Net", "ExchangeR", "Net" * "ExchangeR" AS "EUR Solde" FROM "Paypal" WHERE "Dévise" = 'EUR'

This one is for Euros the others are for GBP and USD. from each there is another query which gives me the SUM at a specified date

SELECT SUM( "EUR Solde" ) FROM "soldeEUR" WHERE "Date" <= :INPUTDATE

all work great, I now wish somehow to combine the three so that I add the date only once and I can SUM the three totals. I have tried to use one query but this does not work, I would like to use a form if possible, I have tried this but I have to add the date three times and I dont know then how to sum the replies, I have saved each query as a table in view section Any suggestions welcome Regards Paul

Here is a screen shot of the query by @Ratslingerimage description

The answers should be (EUR Solde) -66.44, (GBP Solde) 6561.77 & (USD Solde) 68.84

2017-09-30 08:25:11 +0100 received badge  Scholar (source)