MySql Query for opening closing

[phphp]

Sir I have this query and result

[IMG]http://i66.tinypic.com/4r7oee.jpg[/IMG]

Now I want to modify query to get result like this

[IMG]http://i65.tinypic.com/11aihzs.png[/IMG]

Please help

[pritaeas]

Have a look at this SO thread. Am sure it will get you started. If not, come back and explain your problem.

[phphp]

I used these codes

SELECT
c1.date,
SUM(
IFNULL(
cx.purchase - cx.sold + mixed.open_qt,
0
)
) AS opening,
c1.purchase,
c1.sold,
SUM(
IFNULL(
cx.purchase - cx.sold + mixed.open_qt,
0
)
) + IFNULL(c1.purchase - c1.sold,
0) AS closing
FROM
(
SELECT
a.date,
a.qty AS purchase,
b.qty AS sold
FROM
arrival a,
pouring b
WHERE
a.date = b.date
ORDER BY
a.date
) c1
LEFT JOIN
(
SELECT
a.date,
a.qty AS purchase,
b.qty AS sold
FROM
arrival a,
pouring b
WHERE
a.date = b.date
ORDER BY
a.date
) cx ON c1.date > cx.date
GROUP BY
c1.date,
c1.purchase,
c1.sold

MySQL said:

1054 - Unknown column 'mixed.open_qt' in 'field list'

But field mixed.open_qt is present

[pritaeas]

But field mixed.open_qt is present

That might be, but I do not see the table "mixed" mentioned anywhere in your query.

[phphp]

I beleive my query is wrong, i am using 3 tables as
1-mixed
2-arrival
3-pouring

So, I want to get date from these 3 tables

[phphp]

A well known and good reputed Visual Foxpro programmer (Vilhelm-ion-Praisach) provided this working solution.

He can be reached by this link
https://www.facebook.com/vilhelm.praisach
https://www.foxite.com/members/default.aspx?id=97adc192-3121-4dda-ad2d-9b074b4453dd

SELECT
  c1.date,
  SUM(IFNULL(cx.purchase - cx.sold,
  0)) + MAX(p.openqty) AS opening,
  c1.purchase,
  c1.sold,
  SUM(IFNULL(cx.purchase - cx.sold,
  0)) + MAX(p.openqty) + c1.purchase - c1.sold AS closing
FROM
  (
  SELECT
    open_qt AS openqty
  FROM
    mixed
) p
LEFT JOIN
  (
  SELECT
    a.date,
    a.qty AS purchase,
    b.qty AS sold
  FROM
    arrival a,
    pouring b
  WHERE
    a.date = b.date
  ORDER BY
    a.date
) c1 ON c1.purchase + p.openqty > 0
LEFT JOIN
  (
  SELECT
    a.date,
    a.qty AS purchase,
    b.qty AS sold
  FROM
    arrival a,
    pouring b
  WHERE
    a.date = b.date
  ORDER BY
    a.date
) cx ON c1.date > cx.date
GROUP BY
  c1.date,
  c1.purchase,
  c1.sold

[Stuart_4]

https://www.sitepoint.com/community/t/opening-and-closing-mysql-connections/210275