[Solved] Need to modify my query

Creating tables and queries

[Solved] Need to modify my query

Postby mihmih » Sun Nov 01, 2015 7:24 pm

Hi, thats my query. It works good, but i wont something else from my query and i need to modify it:


Code: Select all   Expand viewCollapse view
SELECT

`widok_pozycje_z_terminami`.`okres`,

SUM( ( CASE WHEN `waluty`.`skrot` = 'EUR' THEN `wyroby4`.`cena_wyrobu` * `kursy_walut`.`kurs_euro` ELSE `wyroby4`.`cena_wyrobu` END - ( CASE WHEN `waluty`.`skrot` = 'EUR' THEN `wyroby4`.`cena_wyrobu` * `kursy_walut`.`kurs_euro` ELSE `wyroby4`.`cena_wyrobu` END * `widok_pozycje_z_terminami`.`rabat` / 100 ) ) * `widok_pozycje_z_terminami`.`ilosc` ) AS `sprzedaz`

FROM

`baza`.`widok_pozycje_z_terminami` AS `widok_pozycje_z_terminami`okresy`, `baza`.`zamowienia` AS `zamowienia`, `baza`.`dzialy` AS `dzialy`, `baza`.`zamawiajacy` AS `zamawiajacy`, `baza`.`wyroby4` AS `wyroby4`, `baza`.`waluty` AS `waluty`, `baza`.`kursy_walut` AS `kursy_walut`

WHERE

( `zamowienia`.`ID_dzialu_realizujacego` = `dzialy`.`ID_dzialu` AND `zamowienia`.`ID_zamawiajacego` = `zamawiajacy`.`ID_zamawiajacego` AND `widok_pozycje_z_terminami`.`ID_zamowienia` = `zamowienia`.`ID_zamowienia` AND `widok_pozycje_z_terminami`.`ID_wyrobu` = `wyroby4`.`ID_wyrobu` AND `wyroby4`.`ID_waluty` = `waluty`.`ID_waluty` AND `zamowienia`.`reklamacja` = FALSE AND `zamowienia`.`ID_zamawiajacego` NOT IN ( 4, 5, 7, 9 ) AND `zamowienia`.`czy_wewnetrzne` = FALSE AND `zamawiajacy`.`ID_zamawiajacego` = :a AND `dzialy`.`ID_dzialu` = :b ) OR

( `zamowienia`.`ID_dzialu_realizujacego` = `dzialy`.`ID_dzialu` AND `zamowienia`.`ID_zamawiajacego` = `zamawiajacy`.`ID_zamawiajacego` AND `widok_pozycje_z_terminami`.`ID_zamowienia` = `zamowienia`.`ID_zamowienia` AND `widok_pozycje_z_terminami`.`ID_wyrobu` = `wyroby4`.`ID_wyrobu` AND `wyroby4`.`ID_waluty` = `waluty`.`ID_waluty` AND `zamowienia`.`reklamacja` = FALSE AND `zamowienia`.`ID_zamawiajacego` NOT IN ( 4, 5, 7, 9 ) AND `zamowienia`.`czy_wewnetrzne` = FALSE AND `zamawiajacy`.`ID_zamawiajacego` = :a AND :b IS NULL ) OR

( `zamowienia`.`ID_dzialu_realizujacego` = `dzialy`.`ID_dzialu` AND `zamowienia`.`ID_zamawiajacego` = `zamawiajacy`.`ID_zamawiajacego` AND `widok_pozycje_z_terminami`.`ID_zamowienia` = `zamowienia`.`ID_zamowienia` AND `widok_pozycje_z_terminami`.`ID_wyrobu` = `wyroby4`.`ID_wyrobu` AND `wyroby4`.`ID_waluty` = `waluty`.`ID_waluty` AND `zamowienia`.`reklamacja` = FALSE AND `zamowienia`.`ID_zamawiajacego` NOT IN ( 4, 5, 7, 9 ) AND `zamowienia`.`czy_wewnetrzne` = FALSE AND :a IS NULL AND `dzialy`.`ID_dzialu` = :b ) OR

( `zamowienia`.`ID_dzialu_realizujacego` = `dzialy`.`ID_dzialu` AND `zamowienia`.`ID_zamawiajacego` = `zamawiajacy`.`ID_zamawiajacego` AND `widok_pozycje_z_terminami`.`ID_zamowienia` = `zamowienia`.`ID_zamowienia` AND `widok_pozycje_z_terminami`.`ID_wyrobu` = `wyroby4`.`ID_wyrobu` AND `wyroby4`.`ID_waluty` = `waluty`.`ID_waluty` AND `zamowienia`.`reklamacja` = FALSE AND `zamowienia`.`ID_zamawiajacego` NOT IN ( 4, 5, 7, 9 ) AND `zamowienia`.`czy_wewnetrzne` = FALSE AND :a IS NULL AND :b IS NULL )

  GROUP BY `widok_pozycje_z_terminami`.`okres`

  ORDER BY `widok_pozycje_z_terminami`.`termin` ASC


result of this query are month/year of orderdate and the total value of orders. Parameters that i set (:a and :b) are: firm (who orderer) and department of my firm. For exaple the results of query are:


04.2015 | 585 $
05.2015 | 476 $
07.2015 | 25 $
08.2015 | 1179 $

So when in June there is no orders from firm :a produced by department :b then there is no 06.2015 in my query. And i wont to have:

04.2015 | 585 $
05.2015 | 476 $
06.2015 | 0 $
07.2015 | 25 $
08.2015 | 1179 $

I have no idea how to do it. Probably I need some subquery but i dont know how to comnibe subqery with parameters and "GROUP BY". Can anybode help me?
Last edited by mihmih on Thu Nov 05, 2015 5:56 pm, edited 1 time in total.
LibreOffice 4.2 on Windows7
mihmih
 
Posts: 106
Joined: Wed Feb 18, 2015 9:21 pm

Re: need to modify my query

Postby Villeroy » Sun Nov 01, 2015 10:13 pm

Open a spreadsheet, create a sequence of months, paste the sequence into a one-column database table and use an outer join.

SELECT "Months"."Month", <other fields>, COALSESCE(SUM("Amount"),0) AS "Amount" FROM "Months" LEFT JOIN <other tables> GROUP BY ....
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27742
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: need to modify my query

Postby mihmih » Mon Nov 02, 2015 10:01 am

Thank you for your response

I have a sequence of months:

Code: Select all   Expand viewCollapse view
SELECT DISTINCT `okres` FROM `baza`.`widok_pozycje_z_terminami` AS `widok_pozycje_z_terminami`


Can i paste this subquery to my mainquery? If yes, where? In SELECT, in GROUP BY or in both places?
LibreOffice 4.2 on Windows7
mihmih
 
Posts: 106
Joined: Wed Feb 18, 2015 9:21 pm

Re: need to modify my query

Postby Villeroy » Mon Nov 02, 2015 3:29 pm

It is a record set. It belongs to the FROM clause.
Code: Select all   Expand viewCollapse view
SELECT bla, bla,
FROM `other tables` RIGHT JOIN (SELECT DISTINCT `okres` FROM `baza`.`widok_pozycje_z_terminami` AS `widok_pozycje_z_terminami`)AS `OK` ON `OK`.`okres` = `other`.`okres`

This introduces your column okres in a temporary table named `OK` listing all OK records and Null values where the other record sets have no corresponding okre. In case of Null, COALESCE(SUM(`amounts`),0) AS `amounts` replaces any Null with a zero value.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27742
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: need to modify my query

Postby mihmih » Tue Nov 03, 2015 10:41 am

I'm doing something wrong....

I create a simple database, in my query i wont to have third record:

0 | customer3

where is the error in my query?
Attachments
Nowa Baza Danych.odb
(5.95 KiB) Downloaded 65 times
LibreOffice 4.2 on Windows7
mihmih
 
Posts: 106
Joined: Wed Feb 18, 2015 9:21 pm

Re: need to modify my query

Postby F3K Total » Tue Nov 03, 2015 12:06 pm

Not simple, maybe someone has a better one, but seems to work
Code: Select all   Expand viewCollapse view
SELECT
    "customername",
    "ID_product",
    COALESCE("nnn",0) "nnn"
from
    (SELECT DISTINCT
        "customername",
        "nnn",
        "products"."ID_product"
    FROM
        "customers",
        "products"
    LEFT JOIN
        (SELECT
            "orders"."ID_customer",
            "items"."ID_product",
            SUM("items"."quantity")"nnn"
        FROM
            "items",
            "orders"
        WHERE
            "items"."ID_order" = "orders"."ID_order"
        group by
            "orders"."ID_customer",
            "items"."ID_product") AS X
    ON "customers"."ID_customer" = X."ID_customer"
    AND "products"."ID_product" = X."ID_product"
    AND "products"."ID_product" = 0)

R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: need to modify my query

Postby mihmih » Tue Nov 03, 2015 2:43 pm

yes, it works, thanks.

Now i have to translate it to the query from the first post ...wish me luck:)
LibreOffice 4.2 on Windows7
mihmih
 
Posts: 106
Joined: Wed Feb 18, 2015 9:21 pm

Re: need to modify my query

Postby F3K Total » Tue Nov 03, 2015 3:07 pm

OK, good Luck ;)
The problem was to display ID_product and customername although they where not in orders or items. So you have to display all of them, then left join on orders and items ...
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: need to modify my query

Postby F3K Total » Tue Nov 03, 2015 4:52 pm

Hi,
maybe better to understand, a three step version
  • 01_qCP
    Code: Select all   Expand viewCollapse view
    SELECT DISTINCT "products"."ID_product", "customers"."customername", "customers"."ID_customer", "products"."productname" FROM "customers", "products"
  • 02_qIO
    Code: Select all   Expand viewCollapse view
    SELECT "orders"."ID_customer", "items"."ID_product", SUM( "items"."quantity" ) "nnn" FROM "items", "orders" WHERE "items"."ID_order" = "orders"."ID_order" GROUP BY "orders"."ID_customer", "items"."ID_product"
  • 03_qCN
    Code: Select all   Expand viewCollapse view
    SELECT "01_qCP"."customername", "01_qCP"."ID_product", IFNULL( "02_qIO"."nnn", 0 ) "nnn" FROM "01_qCP" LEFT JOIN "02_qIO" ON "01_qCP"."ID_product" = "02_qIO"."ID_product" AND "01_qCP"."ID_customer" = "02_qIO"."ID_customer" WHERE "ID_product" = 0
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: need to modify my query

Postby mihmih » Wed Nov 04, 2015 12:10 pm

1. I came to the conclusion that i dont need this missing months:) major companies have every month some order so its not a big problem. And when the new company will join a year later, will be not empty months from the beginning of the database.

2. The second thing is ...I thought so far that I know well SQL:) I tried to translate this solution to my query but I was overwhelmed. Thank you very much for your help and ask for another thing:

in my query from the first post i wont to have a third column: ratio (proportion) : sprzedaz/ MAX (sprzedaz)


so i save my query as `historia_sprzedazy` (in this query everythink is ok) and make a query from query:


Code: Select all   Expand viewCollapse view
SELECT `okres`, `sprzedaz`, (`sprzedaz`/ (SELECT MAX( `sprzedaz`) FROM `historia_sprzedazy`)) AS "partofmax" FROM `historia_sprzedazy`


and i have an error:

Stan SQL: 07001
No value specified for parameter 9

a query works well
a subguery works well
together does not work
LibreOffice 4.2 on Windows7
mihmih
 
Posts: 106
Joined: Wed Feb 18, 2015 9:21 pm

Re: need to modify my query

Postby mihmih » Thu Nov 05, 2015 5:56 pm

it may be useful to someone...

Code: Select all   Expand viewCollapse view
SELECT `widok_pozycje_z_terminami`.`okres`, SUM(...),(SELECT MAX(...) FROM (SELECT Sum(...) FROM ......... WHERE .............GROUP BY `widok_pozycje_z_terminami`.`okres`) AS ....) AS .......... FROM ........ WHERE .......' GROUP BY miesiac `widok_pozycje_z_terminami`.`okres`
LibreOffice 4.2 on Windows7
mihmih
 
Posts: 106
Joined: Wed Feb 18, 2015 9:21 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest