0

Hi, I am using UNION ALL to get Sum of expense of different categories from various tables.
Now I got Site against their Sum of expenses but When I make this query as table(tbl2) ans select site,Max(Expense) from tbl2 it shows correct Max(Expense) but first Site id of tbl2.
Here is my query

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
1
Contributor
2
Replies
3
Views
7 Years
Discussion Span
Last Post by ayesha789
1

Its solved

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)
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.