2

I have two tables which represent bills.
List of bills and content of bills each with his relevant data which should be logically "connected" to get proper result. Example table column names are pretty self descriptive.

DROP TABLE IF EXISTS my_list;
CREATE TABLE my_list (indexl int PRIMARY KEY, docnum int, mytime text, rebate double precision, status text);

INSERT INTO my_list    
(indexl, docnum,  mytime,              rebate, status) VALUES 
    (10,      5,  '01.01.2014 15:20:31',    0,    ''), 
    (11,      6,  '02.01.2014 11:10:11',   10,    ''),  
    (12,      7,  '02.01.2014 09:15:01',    0,    ''), 
    (14,      8,  '03.01.2014 12:12:49',   12,    ''),  
    (17,      9,  '04.01.2014 08:19:19',   10,    ''), 
    (18,     10,  '04.01.2014 10:10:10',    0,   'S'), 
    (19,     11,  '04.01.2014 01:04:14',    0,   'B'),
    (21,     12,  '05.01.2014 02:49:14',    0,    ''),
    (22,     13,  '12.01.2014 08:12:17',    0,    '');

DROP TABLE IF EXISTS my_content;
CREATE TABLE my_content (indexc int PRIMARY KEY, docnum int, code int, 
       aname text, price double precision, qty double precision, secondtax double precision, meas text);

INSERT INTO my_content 
(indexc, docnum, code, aname,      price, qty, secondtax, meas) VALUES 
    (10,      5,  587, 'spaghetti',   75,   1,         0, 'kg'), 
    (15,      6, 3432, 'salt',         3,   2,         0, 'kg'), 
    (16,     12,   32, 'olive oil',    4, 1.5,         5, 'kg'), 
    (29,      7, 3432, 'salt',         3,   1,         0, 'kg'), 
    (17,      6,  449, 'sugar',        5,   2,         0, 'kg'), 
    (18,      7, 1582, 'dried eggs',  50, 1.2,         0, 'kg'), 
    (19,      8,  210, 'tomato',      80, 5.5,         0, 'kg'), 
    (20,      9,  211, 'mustard',      5,   3,         5, 'kg'), 
    (22,     10, 2014, 'clove',        1,   1,         0, 'kg'), 
    (23,      9,    8, 'oil',        120,   4,         0, 'lit'), 
    (24,     11,  816, 'laurel',       4,   1,         0, 'kg'), 
    (25,      8, 1582, 'dried eggs',  10, 0.2,         0, 'kg'), 
    (26,     12,   32, 'olive oil',    4,   1,         0, 'kg'), 
    (28,     13,   67, 'corned beef', 40, 0.5,         0, 'kg');

For analyzing those bills I make a query which work almost good but It is sure it can be written better, shorter, more elegant and more proper. 

SELECT s.code, 
   s.aname, 
   SUM(   s.qty) AS sumused, 
   SUM( s.price * s.qty) AS bruttoprice,
   SUM((          s.price/100 * l.rebate) * s.qty) AS sumrebate, 
   SUM((s.price - s.price/100 * l.rebate) * s.qty) AS clearprice,
   SUM((s.price - s.price/100 * l.rebate) * s.qty/100 * s.secondtax) AS sumsecondtax,  
   SUM((s.price - s.price/100 * l.rebate) * s.qty - (s.price - s.price/100 * l.rebate) * s.qty/100 * s.secondtax) AS sumwithoutsecondtax  
FROM   my_content s, my_list l 
WHERE  s.docnum = l.docnum 
       AND NOT l.status='S' 
       AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59' 
GROUP  BY s.code, s.aname, l.status 
ORDER  BY sumused

1) Is it possible to replace expression in query '(s.price/100 * l.rebate) * s.qty' with variable so it haven't to be written all the time? Ideal will be if I can write for example SUM(clearprice * s.secondtax)

2) In case when bill have satus 'S' in list his rows in content have to be skipped what is solved with condition AND NOT l.status='S'. But if status is 'B' that mean that values which have that docnum have to be subtracted (not added) to SUM. Maybe more elegant solution will be to multiply quantity in such rows with value 0-qty.
In showed query status 'B' is ignored because I don't know how to apply it. How to do that?

3) I would actually need to group result only by s.code but I cannot remove s.aname and l.status from GROUP BY because then query don't want to work. In actual situation if I change name for certain code it will be shown separately what is not wanted.
Is it possible to get result grouped only by code but that s.aname (say last one) will be shown in result?

I do all I can for creating example tables and query on easy/instant way.
Please help on concrete question with advice or/and example.
Thanks.

EDIT I solved my query by help of 'kordirko'...

SELECT code, 
   MAX(aname)            AS aname, 
   SUM(newqty)           AS sumused, 
   SUM(price   * newqty) AS bruttoprice,
   SUM(crebate * newqty) AS sumrebate, 
   SUM(cprice  * newqty) AS clearprice,
   SUM(cprice  * newqty/100 * secondtax) AS sumsecondtax,  
   SUM(cprice  * newqty - cprice * newqty/100 * secondtax) AS sumwithoutsecondtax 
FROM (
   SELECT s.*, l.*,
          s.price/100 * l.rebate AS crebate,
          s.price - s.price/100 * l.rebate AS cprice, 
          CASE WHEN l.status = 'B' THEN 0 - s.qty ELSE s.qty END AS newqty  
   FROM   my_content s, my_list l 
   WHERE  s.docnum = l.docnum 
   AND NOT l.status='S' 
   AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59'
 ) AS someAliasWhichhavetobehere
GROUP BY code 
ORDER BY sumused;

Everything seem's OK only I have values -0 in result but I think that will not cause problems on further calculations. If will how can I get rid of that?

Improve this question

1 Answer 1

Reset to default
4

Question 1

Yes, it is possible using a subquery.
A variable some_variable is calculated in the subquery, and it's used in the outer query in this way:

SELECT code, 
   aname, 
   SUM(     qty) AS sumused, 
   SUM(   price *  qty) AS bruttoprice,
   SUM((          some_variable) * qty) AS sumrebate, 
   SUM((price - some_variable) * qty) AS clearprice,
   SUM((price - some_variable) * qty/100 * secondtax) AS sumsecondtax,  
   SUM((price - some_variable) * qty - (price - some_variable) * qty/100 * secondtax) AS sumwithoutsecondtax 
FROM (
  SELECT s.*, l.*,
         s.price/100 * l.rebate some_variable
  FROM   my_content s, my_list l 
  WHERE  s.docnum = l.docnum 
         AND NOT l.status='S' 
         AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59'
) as Alias
GROUP  BY code, aname, status 
ORDER  BY sumused ;

Please take a look at the first query in this --> demo

Question 2

Please give more details. It's not clear which exactly values have to be substracted. Do you mean price, or qty or the whole expression, or something else ?

Generally speaking this can be done using CASE ... WHEN .. THEN expression, for example:

SELECT ....
     SUM (   CASE WHEN status = 'B' 
                  THEN - price * qty
                  ELSE  price * qty
             END
          ) As column_alias,
     ....
FROM ....

or maybe in this way: (when status = 'B' then multiply by -1, otherwise multiply by 1):

SUM (   price * qty * CASE WHEN status = 'B' THEN -1 ELSE 1 END )

Question 3

I would use MAX or MIN function, this is the easiest way. They retrieve a random name (maximun or minimum).

SELECT code, 
   max( aname ) aname, 
   SUM(     qty) AS sumused, 
   SUM(   price *  qty) AS bruttoprice,
   SUM((          some_variable) * qty) AS sumrebate, 
   SUM((price - some_variable) * qty) AS clearprice,
   SUM((price - some_variable) * qty/100 * secondtax) AS sumsecondtax,  
   SUM((price - some_variable) * qty - (price - some_variable) * qty/100 * secondtax) AS sumwithoutsecondtax 
FROM (
  SELECT s.*, l.*,
         s.price/100 * l.rebate some_variable
  FROM   my_content s, my_list l 
  WHERE  s.docnum = l.docnum 
         AND NOT l.status='S' 
         AND l.mytime BETWEEN '02.01.2014 00:00:00' AND '05.01.2014 23:59:59'
) as Alias
GROUP  BY code, status 
ORDER  BY sumused ;

Please take a look at the second query in this --> demo

Question 4 - what is it: s.* and l.*

Basically this is my (bad?) habit coming from Oracle SQL :)

Everybody knows this syntax:
SELECT * FROM sometable

* which means: give me all columns from the table.

Say we want to get all columns, but we want also show a calculated value in the resultset, the obvious way is :

SELECT *, 
       price * qty AS amount
FROM table

This works pretty fine in PostGeSQL, but unfortunately in Oracle this gives a syntax error.
Oracle forces us to use a table name, or an alias:

SELECT table.*, 
       price * qty AS amount
FROM table;

SELECT t.*, 
       price * qty AS amount
FROM table t

Similary when using joins - this work pretty fine:

SELECT *
FROM table1 JOIN table2 ON ....

but this gives a syntax error in Oracle:

SELECT *,
       price * qty AS amount
FROM table1 JOIN table2 ON ....

we must use table names or aliases here:

SELECT table1.*,
       table2.*,
       price * qty AS amount
FROM table1 JOIN table2 ON ....

SELECT t1.*,
       t2.*,
       price * qty AS amount
FROM table1 t1  JOIN table2 AS t2  ON ....

Fortunately this is compliant with ANSI SQL syntax, and should work on all databases :)

Question 5 Why do you name subquery result AS Alias

Because PostgeSQL reqires to give a name (an alias) to the subquery.
Similary MySql also needs the alias:

SELECT ....
FROM(
   subquery
) some_name

... or ....

SELECT ....
FROM(
   subquery
) AS some_name

Only in Oracle the subquery doesn't reqiure the alias (but could be) , in Oracle this works fine:

SELECT ....
FROM (
  subquery ....
)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hello kodirko, thank you very much for helping. I need some more time to try to understand your advices/examples. What mean s.*, l.*, in subquery, all fields? Regarding question 2. "B" mean that selled article is returned back and all values which will normaly be calculated as positive have to be calculated as negative. I think that may be achieved by multiplying with negative quantity (0-quantity) but I may be wrong. 3 solution I understand. It is perfect!
Solution 1 I also use successfully. Why do you name subquery result AS Alias when you never use that name later in code?
No, problem 2 I cant solve yet. Would be maybe possible to use same subquery for help in that? FROM (SELECT s.*, l.*, s.price/100 * l.rebate AS crebate, CASE WHEN l.status = 'B' THEN qty = -1 * qty ELSE qty = 1 * qty END AS newqty FROM my_content s, my_list l... And then use value of newqty in first SELECT? I try but I get error here: SUM(newqty) AS sumused,...
I've added additional explanations to my answer, please take a look at it.
Excellent! Thanks for all informations. I solved my query finally and post it up, to question. Please tell if something is incorrect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.