how to join more then two table in postgresql?

i am running following query

select 
    to_char(sale_order.date_order ,'DD-MM-YYYY') , sum(sale_order.amount_total) as amount

from 
    public.sale_order
where
   sale_order.order_year = '2015' and
   sale_order.order_month = 'April'
group by 
   to_char(sale_order.date_order ,'DD-MM-YYYY') order by to_char(sale_order.date_order ,'DD-MM-YYYY') asc

its giving correct output

   to_char..     amount

"14-04-2015";   1298.00
"15-04-2015";   4294.00
"16-04-2015";   1398.00
"17-04-2015";   1927.00
"18-04-2015";   3094.00
"19-04-2015";   6988.00
"20-04-2015";   6641.00
"21-04-2015";   3045.00

but i am trying to enter a condition which have multiple table connection then it gives different amount value

select 
    to_char(sale_order.date_order ,'DD-MM-YYYY') , sum(sale_order.amount_total) as amount

from 
    public.sale_order ,
    public.sale_order_line , 
    public.product_product ,
    public.product_template ,
    public.product_category

where
    sale_order_line.product_id = product_product.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  sale_order.id = sale_order_line.order_id AND
  sale_order_line.product_id = product_product.id AND 
  product_product.product_tmpl_id = product_template.id AND 
  product_template.categ_id = product_category.id AND
  product_category.name = 'Starchi' and
    sale_order.order_year = '2015' and
  sale_order.order_month = 'April'
group by to_char(sale_order.date_order ,'DD-MM-YYYY') order by to_char(sale_order.date_order ,'DD-MM-YYYY') asc

then it gives different output

  to_char         amount
"14-04-2015";    1298.00
"15-04-2015";    4294.00
"16-04-2015";    1398.00
"17-04-2015";    2805.00     //wrong output
"18-04-2015";    6188.00    //wrong output
"19-04-2015";    13976.00  //wrong output
"20-04-2015";    19229.00  //wrong output
"21-04-2015";    3045.00

what is the exact problem please anybody can tell ?

and how to solve it ?

Answers


[USING @Patrick 's cleaned up code] Most probable cause is that an 1:N relation exists between orders and order_lines, causing the orders with multiple order_lines to be included in the sum() more than once.

SELECT to_char(o.date_order, 'DD-MM-YYYY') AS date_order
        , sum(o.amount_total) AS amount
FROM public.sale_order o
WHERE EXISTS ( SELECT *
        -- These tables probably have an N:1 relation
        -- to public.sale_order
        -- We don't have to sum them; just check existence.
        FROM public.sale_order_line l
        JOIN public.product_product pp ON pp.id = l.product_id
        JOIN public.product_template pt ON pt.id = pp.product_tmpl_id
        JOIN public.product_category pc ON pc.id = pt.categ_id
        WHERE pc.name = 'Starchi'
        AND l.order_id = o.id
        )
  AND o.order_year = '2015'
  AND o.order_month = 'April'
GROUP BY o.date_order
ORDER BY o.date_order ASC
        ;

You made a mistake in your WHERE clauses, as Gordon Linoff already commented, leading to duplicate rows being retrieved and then summed up to give wrong totals. Following wildplasser's advice in his comment, using the JOIN syntax is much cleaner and less error-prone. Your query would look like this:

SELECT to_char(o.date_order, 'DD-MM-YYYY') AS "date", sum(o.amount_total) AS amount
FROM public.sale_order o
JOIN public.sale_order_line l ON l.order_id = o.id
JOIN public.product_product pp ON pp.id = l.product_id
JOIN public.product_template pt ON pt.id = pp.product_tmpl_id
JOIN public.product_category pc ON pc.id = pt.categ_id
WHERE pc.name = 'Starchi'
  AND o.order_year = '2015'
  AND o.order_month = 'April'
GROUP BY o.date_order
ORDER BY o.date_order ASC

Need Your Help

PtrSafe Attribute Error MS Access

forms vba ms-access

I am trying to enter records into an MS Access form and am getting the following compile error:

SWIFT - Is it possible to save audio from AVAudioEngine, or from AudioPlayerNode? If yes, how?

swift avasset avaudioengine avaudioplayernode avaudiofile

I've been looking around Swift documentation to save an audio output from AVAudioEngine but I couldn't find any useful tip.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.