Sub Query Problem

Question

ayesha789 7 Posting Pro in Training

14 Years Ago

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

php

1 Contributor
2 Replies
148 Views
8 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by ayesha789

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ayesha789 7 Posting Pro in Training · Answer 1 · 2010-08-02T14:05:42+00:00

I tried this

as tbl2 having Expense=Max(Expense)

But its not working.

ayesha789 7 Posting Pro in Training · Answer 2 · 2010-08-02T16:04:01+00:00

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)