5 Quries conversion to one query

[ayesha789]

Hi , I have 5 quries, each query showing expense of different category.
like Lease,Diesel,Security,Electricity, Maintenance. 3 quries using 6 tables and 2 quries using 2 tables means 6 tables used in total. Now I want To Show Expense Using one query so I can Get complete Sum of Expenses.
Here are my All Queries
Diesel: (Using 2 Tables Diesel info and Rates(its uesed in onother 2 quries also))

$result = mysql_query("SELECT FORMAT(ROUND( Qty * Itemrate ),2) FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH(rates.Date ) 
WHERE diesel_info.Code =$a AND MONTH( diesel_info.Date )=$m ");
$row = mysql_fetch_array($result);
echo $row[0];

Lease: (USING ONE Table lims_payment)

SELECT FORMAT(ROUND(SUM(`Amount`/12)),2) 
FROM  `lims_payment` 
WHERE  `Code` =$a AND YEAR(DurationS)=$y

Electricity: (Table name is eims_center)

$result = mysql_query("SELECT FORMAT(`GrossAmountWD`,2) FROM  `eims_center` 
WHERE  `Code` =$a AND `BillMonth`='$me'");
$row = mysql_fetch_array($result);
echo $row[0];

Security:(Table Name is securityguards and other table is rates used 2nd time)

$result = mysql_query("SELECT FORMAT(Rates.ItemRate,2) FROM securityguards INNER JOIN rates ON securityguards.Company = rates.item
WHERE securityguards.SiteId =$a");
$row = mysql_fetch_array($result);
echo $row[0];

Outsourcing:(table 1 is info and second table is rates used 3rd time

$result = mysql_query("SELECT FORMAT(Rates.ItemRate,2) FROM info INNER JOIN rates ON info.MaintainedBy = rates.item WHERE info.Code =$a");
$row = mysql_fetch_array($result);
echo $row[0];

All these queries showing expense of each category but now i want to make one query which shows sum of result of all individual queries. creating temporary table is a better idea if any one knows. please suggest.

[rch1231]

Hello,

If your output has the same number of fields with the same field names you can use UNION between the queries to generate a combined format and then sum it. Here is what merging the first two queries would look like. You will probably have to do a little tweaking but I think you will get the idea. From experience, get the first two working before you add the others. I usually get each query generating the output I want then merge them one at a time.

$result = mysql_query(
(
SELECT 'Diesel' as Category, FORMAT(ROUND( Qty * Itemrate ),2) as Category_Total
from `diesel_info`
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH(rates.Date )
WHERE diesel_info.Code =$a AND MONTH( diesel_info.Date )=$m 
)
UNION
(
SELECT 'Lims Payment' as Category, FORMAT(ROUND(SUM(`Amount`/12)),2) as Category_Total 
FROM  `lims_payment` 
WHERE  `Code` =$a AND YEAR(DurationS)=$y
)
)
$row = mysql_fetch_array($result);
echo $row[0];

[ayesha789]

I used it like this

select 'Total' As category,sum(category_total) from

(SELECT  'Diesel' AS Category, ROUND( Qty * Itemrate ) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) 
WHERE diesel_info.Code =4583
AND MONTH( diesel_info.Date ) =06
UNION ALL (

SELECT  'Lims Payment' AS Category,  ROUND( SUM(  `Amount` /12 ))  AS Category_Total
FROM  `lims_payment` 
WHERE  `Code` =4583
AND YEAR( DurationS ) =2010
)) as tbl

[ayesha789]

Thanks for Great idea , Now its working perfectly

select 'Total' As category,sum(category_total) from
 
(SELECT  'Diesel' AS Category, ROUND( Qty * Itemrate ) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) 
WHERE diesel_info.Code =4583
AND MONTH( diesel_info.Date ) =06
UNION ALL (
 
SELECT  'Lims Payment' AS Category,  ROUND( SUM(  `Amount` /12 ))  AS Category_Total
FROM  `lims_payment` 
WHERE  `Code` =4583
AND YEAR( DurationS ) =2010
)
UNION ALL (
SELECT 'Electricity' AS Category,electotal AS Category_Total from
((SELECT `GrossAmountWD` as electotal FROM  `eims_north` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_center` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_south` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010')) as eletbl
)
UNION ALL (
SELECT 'Security' AS Category,Rates.ItemRate AS Category_Total FROM securityguards INNER JOIN rates ON securityguards.Company = rates.item
WHERE securityguards.SiteId =4583
)
UNION ALL (
SELECT 'Outsourcing' AS Category,Rates.ItemRate  AS Category_Total FROM info INNER JOIN rates ON info.MaintainedBy = rates.item WHERE info.Code =4583
)


) as tbl

[ayesha789]

But now I have another Problem , I got Expense Successfully and Revenue Successfully But when I Union all Revenue with Expense Query it give errors. Can you help me if I share my query

[ayesha789]

This query showing error #1054 - Unknown column 'expense' in 'field list'

Select 'ProfitLoss' As Category,Revenue-expense as ProfitLoss from
((SELECT 'Reveneue' AS Category,ROUND(Amount) as Revenue FROM  `Revenue` 
WHERE Revenue.Code =4583 AND MONTH( Revenue.Date )=06 ) 
UNION ALL
(
select 'Total' As category,sum(category_total) as expense from
 
(SELECT  'Diesel' AS Category, ROUND( Qty * Itemrate ) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) 
WHERE diesel_info.Code =4583
AND MONTH( diesel_info.Date ) =06
UNION ALL (
 
SELECT  'Lims Payment' AS Category,  ROUND( SUM(  `Amount` /12 ))  AS Category_Total
FROM  `lims_payment` 
WHERE  `Code` =4583
AND YEAR( DurationS ) =2010
)
UNION ALL (
SELECT 'Electricity' AS Category,electotal AS Category_Total from
((SELECT `GrossAmountWD` as electotal FROM  `eims_north` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_center` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_south` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010')) as eletbl
)
UNION ALL (
SELECT 'Security' AS Category,Rates.ItemRate AS Category_Total FROM securityguards INNER JOIN rates ON securityguards.Company = rates.item
WHERE securityguards.SiteId =4583
)
UNION ALL (
SELECT 'Outsourcing' AS Category,Rates.ItemRate  AS Category_Total FROM info INNER JOIN rates ON info.MaintainedBy = rates.item WHERE info.Code =4583
)


) as tbl
)
) as tbl2

[rch1231]

I think I see what the problem is. When you use UNION you have to have the same column names in every query. On line 6 instead of

as Category_Total

you have

as expense

. I am not sure but I also noticed on the same line category is spelled with a lower case C and every where else it is capitalized. See if that fixes it and let me know.

[ayesha789]

Now this query showing 2 result in 2 rows , is there any way to subtract two rows after this long auery

Select 'ProfitLoss' As Category,a as ProfitLoss from
((SELECT 'Reveneue' AS Category,ROUND(Amount) as a FROM  `Revenue` 
WHERE Revenue.Code =4583 AND MONTH( Revenue.Date )=06 ) 
UNION ALL
(
select 'Total' As category,sum(category_total) as a from
 
(SELECT  'Diesel' AS Category, ROUND( Qty * Itemrate ) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) 
WHERE diesel_info.Code =4583
AND MONTH( diesel_info.Date ) =06
UNION ALL (
 
SELECT  'Lims Payment' AS Category,  ROUND( SUM(  `Amount` /12 ))  AS Category_Total
FROM  `lims_payment` 
WHERE  `Code` =4583
AND YEAR( DurationS ) =2010
)
UNION ALL (
SELECT 'Electricity' AS Category,electotal AS Category_Total from
((SELECT `GrossAmountWD` as electotal FROM  `eims_north` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_center` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_south` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010')) as eletbl
)
UNION ALL (
SELECT 'Security' AS Category,Rates.ItemRate AS Category_Total FROM securityguards INNER JOIN rates ON securityguards.Company = rates.item
WHERE securityguards.SiteId =4583
)
UNION ALL (
SELECT 'Outsourcing' AS Category,Rates.ItemRate  AS Category_Total FROM info INNER JOIN rates ON info.MaintainedBy = rates.item WHERE info.Code =4583
)


) as tbl
)
) as tbl2

[rch1231]

I think you are trying to get it all on one line with a column showing revenue - costs. If that is correct then add a column to the revenue entry like this:

SELECT 'Reveneue' AS Category,ROUND(Amount) as a, 0 AS Expenses FROM `Revenue`

and then change the total select line from the other selects to:

select 'Total' As category,sum(category_total) AS Expenses, 0 as a from

and finally at the very first select:

Select 'Totals' As Category,a as 'Revenue', Expenses, (a - Expenses) as ProfitLoss

should give you something like this

Category Revenue Expenses ProfitLoss
Totals 1000000.00 24.95 999975.05

By adding a column even if it is 0 to each of the unions you keep the number of columns and column names the same. IF I guessed wrong tell me what you want as the result and I will give it a shot.

[rch1231]

Opps...You will need to sum each of the numbers and group by Category to get just one line.

Select 'Totals' As Category,sum(a) as 'Revenue', sum(Expenses), sum(a - Expenses) as ProfitLoss

.
.
.
Group By Category

[ayesha789]

This query shows result like picture attached . above is revenue and below is Expense nut now its one table i can't subtract 2 resulted rows.

Now this query showing 2 result in 2 rows , is there any way to subtract two rows after this long auery

Select 'ProfitLoss' As Category,a as ProfitLoss from
((SELECT 'Reveneue' AS Category,ROUND(Amount) as a FROM  `Revenue` 
WHERE Revenue.Code =4583 AND MONTH( Revenue.Date )=06 ) 
UNION ALL
(
select 'Total' As category,sum(category_total) as a from
 
(SELECT  'Diesel' AS Category, ROUND( Qty * Itemrate ) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) 
WHERE diesel_info.Code =4583
AND MONTH( diesel_info.Date ) =06
UNION ALL (
 
SELECT  'Lims Payment' AS Category,  ROUND( SUM(  `Amount` /12 ))  AS Category_Total
FROM  `lims_payment` 
WHERE  `Code` =4583
AND YEAR( DurationS ) =2010
)
UNION ALL (
SELECT 'Electricity' AS Category,electotal AS Category_Total from
((SELECT `GrossAmountWD` as electotal FROM  `eims_north` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_center` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_south` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010')) as eletbl
)
UNION ALL (
SELECT 'Security' AS Category,Rates.ItemRate AS Category_Total FROM securityguards INNER JOIN rates ON securityguards.Company = rates.item
WHERE securityguards.SiteId =4583
)
UNION ALL (
SELECT 'Outsourcing' AS Category,Rates.ItemRate  AS Category_Total FROM info INNER JOIN rates ON info.MaintainedBy = rates.item WHERE info.Code =4583
)


) as tbl
)
) as tbl2

[ayesha789]

Opps...You will need to sum each of the numbers and group by Category to get just one line.

Select 'Totals' As Category,sum(a) as 'Revenue', sum(Expenses), sum(a - Expenses) as ProfitLoss

.
.
.
Group By Category

This Query works

Select 'ProfitLoss' As Category,Sum(Revenue),Sum(Expense),Sum(Revenue-Expense) as ProfitLoss from
((SELECT 'Reveneue' AS Category,ROUND(Amount) as Revenue,0 as Expense FROM  `Revenue` 
WHERE Revenue.Code =4583 AND MONTH( Revenue.Date )=06 ) 
UNION ALL
(
select 'Total' As category,0 as Revenue,sum(category_total) as Expense from
 
(SELECT  'Diesel' AS Category, ROUND( Qty * Itemrate ) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) 
WHERE diesel_info.Code =4583
AND MONTH( diesel_info.Date ) =06
UNION ALL (
 
SELECT  'Lims Payment' AS Category,  ROUND( SUM(  `Amount` /12 ))  AS Category_Total
FROM  `lims_payment` 
WHERE  `Code` =4583
AND YEAR( DurationS ) =2010
)
UNION ALL (
SELECT 'Electricity' AS Category,electotal AS Category_Total from
((SELECT `GrossAmountWD` as electotal FROM  `eims_north` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_center` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010') UNION ALL (SELECT `GrossAmountWD` as electotal FROM  `eims_south` 
WHERE  `Code` =4583 AND `BillMonth`='June-2010')) as eletbl
)
UNION ALL (
SELECT 'Security' AS Category,Rates.ItemRate AS Category_Total FROM securityguards INNER JOIN rates ON securityguards.Company = rates.item
WHERE securityguards.SiteId =4583
)
UNION ALL (
SELECT 'Outsourcing' AS Category,Rates.ItemRate  AS Category_Total FROM info INNER JOIN rates ON info.MaintainedBy = rates.item WHERE info.Code =4583
)


) as tbl
)
) as tbl2

[ayesha789]

Now there is another problem, when i select site and Max(Expense) from tbl2 its shows wrong Site against Max(Expense) and take first site and show Max(Expense). This query is for most expensive site in DB.

Select Site,Max(Expense) from
(
Select Site,Sum(Category_Total) as Expense from
(
(
SELECT  'Diesel' AS Category,Code as Site,ROUND(SUM(Qty * Itemrate)) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) Group by Site
)
UNION ALL
(
SELECT  'Lims Payment' AS Category, Code AS Site, ROUND( SUM(  `Amount` /12 ) ) AS Category_Total
FROM  `lims_payment`
GROUP BY Site
)
UNION ALL
(
SELECT 'Electricity' AS Category,Site,electotal AS Category_Total from
((SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_north` Group by Code) UNION ALL (SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_center` Group by Code) UNION ALL (SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_south` Group by Code)) as eletbl group by Site
)
UNION ALL
(SELECT  'Security' AS Category, SiteID AS Site, SUM( Rates.ItemRate ) AS Category_Total
FROM securityguards
INNER JOIN rates ON securityguards.Company = rates.item
GROUP BY Site)
UNION ALL
(SELECT  'Outsourcing' AS Category, Code AS Site, SUM( Rates.ItemRate ) AS Category_Total
FROM info
INNER JOIN rates ON info.MaintainedBy = rates.item
GROUP BY Site)) as tbl group by Site
) as tbl2

[ayesha789]

Its Solved too using

Select site,Expense from
(
Select 'Expense' AS Category,Site,Sum(Category_Total) as Expense from
(
(
SELECT  'Diesel' AS Category,Code as Site,ROUND(SUM(Qty * Itemrate)) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) Group by Site
)
UNION ALL
(
SELECT  'Lims Payment' AS Category, Code AS Site, ROUND( SUM(  `Amount` /12 ) ) AS Category_Total
FROM  `lims_payment`
GROUP BY Site
)
UNION ALL
(
SELECT 'Electricity' AS Category,Site,electotal AS Category_Total from
((SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_north` Group by Code) UNION ALL (SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_center` Group by Code) UNION ALL (SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_south` Group by Code)) as eletbl group by Site
)
UNION ALL
(SELECT  'Security' AS Category, SiteID AS Site, SUM( Rates.ItemRate ) AS Category_Total
FROM securityguards
INNER JOIN rates ON securityguards.Company = rates.item
GROUP BY Site)
UNION ALL
(SELECT  'Outsourcing' AS Category, Code AS Site, SUM( Rates.ItemRate ) AS Category_Total
FROM info
INNER JOIN rates ON info.MaintainedBy = rates.item
GROUP BY Site)) as tbl group by Site
) as tbl2 WHERE Expense=(Select Max(Expense) from
(
Select 'Expense' AS Category,Site,Sum(Category_Total) as Expense from
(
(
SELECT  'Diesel' AS Category,Code as Site,ROUND(SUM(Qty * Itemrate)) AS Category_Total
FROM  `diesel_info` 
INNER JOIN rates ON diesel_info.marker = rates.item
AND MONTH( diesel_info.Date ) = MONTH( rates.Date ) Group by Site
)
UNION ALL
(
SELECT  'Lims Payment' AS Category, Code AS Site, ROUND( SUM(  `Amount` /12 ) ) AS Category_Total
FROM  `lims_payment`
GROUP BY Site
)
UNION ALL
(
SELECT 'Electricity' AS Category,Site,electotal AS Category_Total from
((SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_north` Group by Code) UNION ALL (SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_center` Group by Code) UNION ALL (SELECT Code as Site,SUM(`GrossAmountWD`) as electotal FROM  `eims_south` Group by Code)) as eletbl group by Site
)
UNION ALL
(SELECT  'Security' AS Category, SiteID AS Site, SUM( Rates.ItemRate ) AS Category_Total
FROM securityguards
INNER JOIN rates ON securityguards.Company = rates.item
GROUP BY Site)
UNION ALL
(SELECT  'Outsourcing' AS Category, Code AS Site, SUM( Rates.ItemRate ) AS Category_Total
FROM info
INNER JOIN rates ON info.MaintainedBy = rates.item
GROUP BY Site)) as tbl group by Site
) as tbl2)