[Solved] Need to modify my query

[mihmih]

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

Code: Select all

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?

[Villeroy]

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 ....

[mihmih]

Thank you for your response

I have a sequence of months:

Code: Select all

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?

[Villeroy]

It is a record set. It belongs to the FROM clause.

Code: Select all

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.

[mihmih]

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?

[F3K Total]

Not simple, maybe someone has a better one, but seems to work

Code: Select all

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

[mihmih]

yes, it works, thanks.

Now i have to translate it to the query from the first post ...wish me luck:)

[F3K Total]

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

[F3K Total]

Hi,
maybe better to understand, a three step version

• 01_qCP

  Code: Select all

  SELECT DISTINCT "products"."ID_product", "customers"."customername", "customers"."ID_customer", "products"."productname" FROM "customers", "products"

• 02_qIO

  Code: Select all

  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

  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

[mihmih]

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

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

[mihmih]

it may be useful to someone...

Code: Select all

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`