The Database Forum

**dgkindy** · 05-06-2010, 02:02 AM

I am summing all the values of a specific project, but would like to report out on all value categories(WBS) regardless if there is a value or not.

If I remove the WHERE statement, it will print out correctly but then it grabs everything value for every project, alternatively, if I leave in the WHERE statement, it only shows the WBS lines that have a value.

How do I get both in the same statement without having to create two separate queries.

with WHERE
ENG 55000
PM 25000

without WHERE
Cont NULL
ENG 88000
PM 59000

Quote:

SELECT tbl_list_wbs.wbs, Sum(tbl_project_variance_costing.wares_value*tbl_p roject_variance_costing.wares_qty) AS "total"
FROM tbl_list_wbs LEFT JOIN tbl_project_variance_costing ON tbl_list_wbs.wbs_id= tbl_project_variance_costing.wbs_id
WHERE project_variance_id=3
GROUP BY tbl_list_wbs.wbs

**tripy** · 05-06-2010, 03:25 AM

It's hard to say without having the datas at hand, but your query looks fine.
I'm just not sure if your DB consider that a LEFT JOIN is a LEFT OUTER JOIN or a LEFT INNER JOIN.

Try to run it with an explicit outer join, just to try it:

Code:

SELECT l.wbs, Sum(v.wares_value*v.wares_qty) AS "total"
FROM tbl_list_wbs as l
    LEFT OUTER JOIN tbl_project_variance_costing as v
        ON l.wbs_id= v.wbs_id
WHERE v.project_variance_id=3
GROUP BY l.wbs

**dgkindy** · 05-06-2010, 04:59 AM

I tried as you suggest and no joy.

I have included the mysql statements for the the tables and data as well as screen shots of the two resulting datagrids

Quote:

-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 06, 2010 at 04:45 AM
-- Server version: 5.1.30
-- PHP Version: 5.2.8
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `petdb`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_list_wbs`
--
CREATE TABLE IF NOT EXISTS `tbl_list_wbs` (
`wbs_id` int(11) NOT NULL AUTO_INCREMENT,
`wbs` varchar(30) NOT NULL,
PRIMARY KEY (`wbs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `tbl_list_wbs`
--
INSERT INTO `tbl_list_wbs` (`wbs_id`, `wbs`) VALUES
(1, 'Contingency'),
(2, 'Financials'),
(3, 'Project Management'),
(4, 'Engineering'),
(5, 'Manufacturing'),
(6, 'Procurement'),
(7, 'Field Services');

Quote:

-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 06, 2010 at 04:47 AM
-- Server version: 5.1.30
-- PHP Version: 5.2.8
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `petdb`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_project_variance_costing`
--
CREATE TABLE IF NOT EXISTS `tbl_project_variance_costing` (
`project_variance_costing_id` int(11) NOT NULL AUTO_INCREMENT,
`project_variance_id` int(11) NOT NULL,
`date_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`wbs_id` int(11) DEFAULT NULL,
`discipline_id` int(11) NOT NULL,
`wares` varchar(45) DEFAULT NULL,
`wares_qty` decimal(12,2) NOT NULL DEFAULT '1.00',
`wares_value` decimal(12,2) NOT NULL,
PRIMARY KEY (`project_variance_costing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=16 ;
--
-- Dumping data for table `tbl_project_variance_costing`
--
INSERT INTO `tbl_project_variance_costing` (`project_variance_costing_id`, `project_variance_id`, `date_stamp`, `wbs_id`, `discipline_id`, `wares`, `wares_qty`, `wares_value`) VALUES
(1, 3, '2010-05-03 06:54:23', NULL, 1, 'David Kindy', '10.00', '85.33'),
(2, 3, '2010-05-03 06:54:23', NULL, 4, 'Scott Corney', '20.00', '85.33'),
(3, 3, '2010-05-03 06:54:23', NULL, 7, 'Contract Position', '1.00', '44.00'),
(4, 3, '2010-05-03 06:54:23', NULL, 3, 'GLPH Eng', '1.00', '34.00'),
(5, 3, '2010-05-02 19:03:22', NULL, 4, NULL, '1.00', '33.00'),
(6, 3, '2010-05-03 07:00:15', NULL, 20, 'Dennis Cook', '2000.00', '83.00'),
(7, 3, '2010-05-03 07:00:15', NULL, 21, 'part time person', '2000.00', '115.00'),
(8, 3, '2010-05-03 07:00:15', NULL, 22, 'full time shared with MW', '2000.00', '115.00'),
(9, 3, '2010-05-03 07:00:15', NULL, 23, 'Doug ', '2000.00', '83.00'),
(10, 3, '2010-05-03 07:00:15', NULL, 17, 'Commissioning of Plant', '60.00', '990.00'),
(11, 3, '2010-05-05 09:12:12', 3, 36, 'Air Canada Flight', '1.00', '1200.00'),
(12, 3, '2010-05-05 09:12:12', 4, 37, 'Car to be shared between 3 people', '1.00', '55.00'),
(13, 3, '2010-05-05 09:12:12', 7, 38, 'Hotel to be be shared with 4 FSR', '1.00', '78.00'),
(14, 3, '2010-05-05 11:46:31', 5, 41, 'PRO450', '4.00', '200000.00'),
(15, 3, '2010-05-05 11:46:31', 6, 42, 'Mechanical Contractor', '1.00', '120000.00');

**tripy** · 05-06-2010, 05:47 AM

Thanks for the dump, it's much easier that way.
I think I've got it.

My first understanding was that you where basing this on a project.
But proof reading your question, you are in fact basing your query on the categories, not the projects.

I think you should swap your table definition, or use a right outer join:
(both query produce the same output)

Code:

SELECT l.wbs, Sum(v.wares_value*v.wares_qty) AS "total"
FROM tbl_list_wbs as l
    RIGHT OUTER JOIN tbl_project_variance_costing as v
        ON l.wbs_id= v.wbs_id
WHERE v.project_variance_id=3
GROUP BY l.wbs;

SELECT l.wbs, Sum(v.wares_value*v.wares_qty) AS "total"
FROM tbl_project_variance_costing as v
    LEFT OUTER JOIN tbl_list_wbs as l
        ON l.wbs_id= v.wbs_id
WHERE v.project_variance_id=3
GROUP BY l.wbs;

Both return the same result:

Code:

+--------------------+-------------+
| wbs                | total       |
+--------------------+-------------+
| NULL               | 854070.9000 |
| Engineering        |     55.0000 |
| Field Services     |     78.0000 |
| Manufacturing      | 800000.0000 |
| Procurement        | 120000.0000 |
| Project Management |   1200.0000 |
+--------------------+-------------+
6 rows in set (0.00 sec)

**dgkindy** · 05-06-2010, 01:39 PM

THought you had it there for a moment.

In the WBS side, it should list 5 items plus Contigency and Financials. The resulting table that is displayed should have Null or 0 for both the Contigency and Financials.

**tripy** · 05-06-2010, 04:38 PM

Ok, I'm taking it from the start..
We have this in the wbs:

Code:

+--------+--------------------+
| wbs_id | wbs                |
+--------+--------------------+
|      1 | Contingency        |
|      2 | Financials         |
|      3 | Project Management |
|      4 | Engineering        |
|      5 | Manufacturing      |
|      6 | Procurement        |
|      7 | Field Services     |
+--------+--------------------+

and This in the variants:

Code:

+-----------------------------+---------------------+---------------------+--------+---------------+-----------------------------------+-----------+-------------+
| project_variance_costing_id | project_variance_id | date_stamp          | wbs_id | discipline_id | wares                             | wares_qty | wares_value |
+-----------------------------+---------------------+---------------------+--------+---------------+-----------------------------------+-----------+-------------+
|                           1 |                   3 | 2010-05-03 06:54:23 |   NULL |             1 | David Kindy                       |     10.00 |       85.33 |
|                           2 |                   3 | 2010-05-03 06:54:23 |   NULL |             4 | Scott Corney                      |     20.00 |       85.33 |
|                           3 |                   3 | 2010-05-03 06:54:23 |   NULL |             7 | Contract Position                 |      1.00 |       44.00 |
|                           4 |                   3 | 2010-05-03 06:54:23 |   NULL |             3 | GLPH Eng                          |      1.00 |       34.00 |
|                           5 |                   3 | 2010-05-02 19:03:22 |   NULL |             4 | NULL                              |      1.00 |       33.00 |
|                           6 |                   3 | 2010-05-03 07:00:15 |   NULL |            20 | Dennis Cook                       |   2000.00 |       83.00 |
|                           7 |                   3 | 2010-05-03 07:00:15 |   NULL |            21 | part time person                  |   2000.00 |      115.00 |
|                           8 |                   3 | 2010-05-03 07:00:15 |   NULL |            22 | full time shared with MW          |   2000.00 |      115.00 |
|                           9 |                   3 | 2010-05-03 07:00:15 |   NULL |            23 | Doug                              |   2000.00 |       83.00 |
|                          10 |                   3 | 2010-05-03 07:00:15 |   NULL |            17 | Commissioning of Plant            |     60.00 |      990.00 |
|                          11 |                   3 | 2010-05-05 09:12:12 |      3 |            36 | Air Canada Flight                 |      1.00 |     1200.00 |
|                          12 |                   3 | 2010-05-05 09:12:12 |      4 |            37 | Car to be shared between 3 people |      1.00 |       55.00 |
|                          13 |                   3 | 2010-05-05 09:12:12 |      7 |            38 | Hotel to be be shared with 4 FSR  |      1.00 |       78.00 |
|                          14 |                   3 | 2010-05-05 11:46:31 |      5 |            41 | PRO450                            |      4.00 |   200000.00 |
|                          15 |                   3 | 2010-05-05 11:46:31 |      6 |            42 | Mechanical Contractor             |      1.00 |   120000.00 |
+-----------------------------+---------------------+---------------------+--------+---------------+-----------------------------------+-----------+-------------+

You want to output the wbs with a sum of every variants that is attached to it, right?
So, the base of the query should be the wbs table.

Code:

SELECT l.wbs
FROM tbl_list_wbs as l

Then, adding the variants, we have

Code:

SELECT l.wbs, v.wares_value, v.wares_qty
FROM tbl_list_wbs as l
    LEFT OUTER JOIN tbl_project_variance_costing as v
        ON v.wbs_id=l.wbs_id

which gives us:

Code:

+--------------------+-------------+-----------+
| wbs                | wares_value | wares_qty |
+--------------------+-------------+-----------+
| Contingency        |        NULL |      NULL |
| Financials         |        NULL |      NULL |
| Project Management |     1200.00 |      1.00 |
| Engineering        |       55.00 |      1.00 |
| Manufacturing      |   200000.00 |      4.00 |
| Procurement        |   120000.00 |      1.00 |
| Field Services     |       78.00 |      1.00 |
+--------------------+-------------+-----------+

Which is correct, because the left outer join makes all the 7 records from the wbs table be outputed.
Contingency and Financials have NULL values because no records in the variant table are reffering them.

Adding the computed column, we have:

Code:

SELECT l.wbs, v.wares_value, v.wares_qty, v.wares_value*v.wares_qty AS "total"
FROM tbl_list_wbs as l
    LEFT OUTER JOIN tbl_project_variance_costing as v
        ON v.wbs_id=l.wbs_id

Code:

+--------------------+-------------+-----------+-------------+
| wbs                | wares_value | wares_qty | total       |
+--------------------+-------------+-----------+-------------+
| Contingency        |        NULL |      NULL |        NULL |
| Financials         |        NULL |      NULL |        NULL |
| Project Management |     1200.00 |      1.00 |   1200.0000 |
| Engineering        |       55.00 |      1.00 |     55.0000 |
| Manufacturing      |   200000.00 |      4.00 | 800000.0000 |
| Procurement        |   120000.00 |      1.00 | 120000.0000 |
| Field Services     |       78.00 |      1.00 |     78.0000 |
+--------------------+-------------+-----------+-------------+

And this, I think should be the output you seek.

And if you want the grand total, the easiest way to do that is:

Code:

SELECT sum(x.total)
FROM (
    SELECT l.wbs, v.wares_value, v.wares_qty, v.wares_value*v.wares_qty AS "total"
    FROM tbl_list_wbs as l
        LEFT OUTER JOIN tbl_project_variance_costing as v
            ON v.wbs_id=l.wbs_id
) as x

which gives us

Code:

+--------------+
| sum(x.total) |
+--------------+
|  921333.0000 |
+--------------+

Looking at the previous query:

Code:

SELECT l.wbs, Sum(v.wares_value*v.wares_qty) AS "total"
FROM tbl_list_wbs as l
    LEFT OUTER JOIN tbl_project_variance_costing as v
        ON l.wbs_id= v.wbs_id
WHERE v.project_variance_id=3
GROUP BY l.wbs

Code:

+--------------------+-------------+
| wbs                | total       |
+--------------------+-------------+
| NULL               | 854070.9000 |
| Engineering        |     55.0000 |
| Field Services     |     78.0000 |
| Manufacturing      | 800000.0000 |
| Procurement        | 120000.0000 |
| Project Management |   1200.0000 |
+--------------------+-------------+

The problem is the SUM() and GROUPing.
The sum() is done on every computatin result, effectively making 1 row.
The grouping allows you to split this row back to several, but as both Contingency and Financials are not referenced in the variants table, they are both grouped into the NULL value with the sum of every variant rows where wbs_id is null.

**dgkindy** · 05-06-2010, 05:44 PM

Thanks for the help. Yes I will need the sumtotal that you demostrated. I have realized now my problem and of course will lead to another question.

**dgkindy** · 05-07-2010, 02:18 AM

Alright, I am back got it working. It has morphed into a farely large statement. Thanks for your help.

Quote:

SELECT tbl_list_wbs.wbs, tbl_b.subtotal
From(
SELECT tbl_a.wbs_id, SUM(tbl_a.wares_value*tbl_a.wares_qty) AS subtotal
FROM ((
SELECT tbl_project_variance_costing.wares_value, tbl_project_variance_costing.wares_qty, tbl_list_wbs.wbs_id
FROM tbl_list_wbs
INNER JOIN (tbl_list_discipline
INNER JOIN tbl_project_variance_costing ON tbl_list_discipline.discipline_id = tbl_project_variance_costing.discipline_id) ON tbl_list_wbs.wbs_id = tbl_list_discipline.wbs_id
WHERE project_variance_id =3
AND tbl_project_variance_costing.wbs_id IS NULL
)
UNION ALL
(
SELECT tbl_project_variance_costing.wares_value, tbl_project_variance_costing.wares_qty, tbl_list_wbs.wbs_id
FROM tbl_list_wbs
RIGHT OUTER JOIN tbl_project_variance_costing ON tbl_list_wbs.wbs_id = tbl_project_variance_costing.wbs_id
WHERE project_variance_id =3
AND tbl_list_wbs.wbs IS NOT NULL)
) AS tbl_a
GROUP BY tbl_a.wbs_id
) AS tbl_b
RIGHT JOIN tbl_list_wbs ON tbl_b.wbs_id =tbl_list_wbs.wbs_id

**rajiv kumar B** · 05-11-2010, 12:49 AM

--Only change I ve done is Added IS NULL validation

SELECT tbl_list_wbs.wbs,
Sum(
ISNULL(tbl_project_variance_costing.wares_value,0) *
ISNULL(tbl_p roject_variance_costing.wares_qty,0)
) AS "total"
FROM tbl_list_wbs LEFT JOIN tbl_project_variance_costing ON
tbl_list_wbs.wbs_id= tbl_project_variance_costing.wbs_id
WHERE project_variance_id=3
GROUP BY tbl_list_wbs.wbs