subversivetech
asked on
Transact SQL nested select
Dont know if I am going about this the right way. I am trying:
SELECT Industries.IndustryDesc, sum(LinkClientItems.Value)
(
SELECT sum(LinkClientItems.Value)
FROM Industries INNER JOIN
Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID
)as Sell
FROM Industries INNER JOIN
Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID
group by industries.industrydesc
This is giving :
Advertising; Media; Printi 700.00 223.00
Building; Construction 5300.00 223.00
Manufacturing 500.00 223.00
Professional 500.00 223.00
The first sum column is correct, but the second is simply the first sum repeated. Not grouping properly (or something). My knowledge of SQL is not great and I dont usually go past simple joins for queries. Any help would be appreciated.
SELECT Industries.IndustryDesc, sum(LinkClientItems.Value)
(
SELECT sum(LinkClientItems.Value)
FROM Industries INNER JOIN
Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID
)as Sell
FROM Industries INNER JOIN
Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID
group by industries.industrydesc
This is giving :
Advertising; Media; Printi 700.00 223.00
Building; Construction 5300.00 223.00
Manufacturing 500.00 223.00
Professional 500.00 223.00
The first sum column is correct, but the second is simply the first sum repeated. Not grouping properly (or something). My knowledge of SQL is not great and I dont usually go past simple joins for queries. Any help would be appreciated.
Think you want to filter the subquery based on the current industry you are on.
SELECT a.IndustryDesc, sum(LinkClientItems.Value) as CoreExpenses,
(
SELECT sum(LinkClientItems.Value) as Sell
FROM Industries b INNER JOIN
Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID = 2 AND a.IndustryDesc = b.IndustryDesc)
)as Sell
FROM Industries a INNER JOIN
Categories ON a.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID = 1)
group by a.industrydesc
ASKER
Executing that gives:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Industries.IndustryID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Industries.IndustryID" could not be bound.
Sorry I missed one. You have to qualify all the references of Industries by aliases if used and used aliases to tell which one I intend since crossing between outer query and inner one in where clause.
SELECT a.IndustryDesc, sum(LinkClientItems.Value) as CoreExpenses,
(
SELECT sum(LinkClientItems.Value) as Sell
FROM Industries b INNER JOIN
Categories ON b.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID = 2 AND a.IndustryDesc = b.IndustryDesc)
)as Sell
FROM Industries a INNER JOIN
Categories ON a.IndustryID = Categories.IndustryID INNER JOIN
Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE (LinkClientItems.ItemUseID = 1)
group by a.industrydescASKER
This works the way I want on some test data:
SELECT industry, sum(Value) as Boating,
(
SELECT sum(Value) as Cars
FROM Test
where category = 'Cars'
) as Cars
FROM Test
where category = 'Boating'
group by industry
This is against a single table with 3 fields:
Industry , category, value
SELECT industry, sum(Value) as Boating,
(
SELECT sum(Value) as Cars
FROM Test
where category = 'Cars'
) as Cars
FROM Test
where category = 'Boating'
group by industry
This is against a single table with 3 fields:
Industry , category, value
That will get you exactly what you have above. If that is what you intend then the result is correct.
You are getting a sum of all the values where category = 'Cars' regardless of industry or whatever else is being used in outer query. If you have multiple rows in the outer query, all the rows will have the same sum.
If you want different sums by row, you must use a value from each row as the criteria for the sum.
Hope that helps.
You are getting a sum of all the values where category = 'Cars' regardless of industry or whatever else is being used in outer query. If you have multiple rows in the outer query, all the rows will have the same sum.
If you want different sums by row, you must use a value from each row as the criteria for the sum.
Hope that helps.
ASKER
You solution is very close to what I am looking for. It is looking good, but it does not give rows that have a null value for the 'coreexpenses' column. Any idea to make sure they are included?
Your solution gives:
Advertising; Media; Printing 700.00 NULL
Building; Construction 5300.00 23.00
Manufacturing 500.00 NULL
Professional 500.00 NULL
But it should include
Health null 200
Cheers for the help!
Your solution gives:
Advertising; Media; Printing 700.00 NULL
Building; Construction 5300.00 23.00
Manufacturing 500.00 NULL
Professional 500.00 NULL
But it should include
Health null 200
Cheers for the help!
ASKER
Further,
I need every IndustryDesc returned regardless of whether there are any LinkClientItems for that Industry
I need every IndustryDesc returned regardless of whether there are any LinkClientItems for that Industry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mate you are a legend!
Thanks from down under. I am finding it difficult to get my head around the more advanced T-SQL. I find that the Microsoft docs and MSDN alway use overly complex examples. Not sure if you could recommend a resource to use as a reference as I work my way up?
Cheers.
Thanks from down under. I am finding it difficult to get my head around the more advanced T-SQL. I find that the Microsoft docs and MSDN alway use overly complex examples. Not sure if you could recommend a resource to use as a reference as I work my way up?
Cheers.
I usually find the "In A Nutshell" books from O'Reilly and/or the Wrox books pretty helpful.
Open in new window