May 18, 2017 at 9:53 am
I'm having some trouble getting the proper logical output with the following code:
select tlorder.destcity as "Destination", count(*) as "Number of Orders", sum(total_charges), sum(order_interliner.functional_amt) as "Total Partner Costs"
from tlorder, order_interliner
where tlorder.detail_line_id = order_interliner.detail_line_id
and destprov = 'ON'-- or origprov = 'OH'
and origcity = 'COLUMBUS'
and created_time >= '2017-4-1'
group by destcity
order by order_interliner.functional_amt
Fetch First 100 Rows Only
Where I'm drawing data from two tables - tlorder and order_interliner.
I keep getting the error that states that the column functional_amt is specified in a SELECT clause, but is not found in the GROUP BY clause. My first attempt was to add 'functional_amt' and 'total_charges' into the group by clause, but that did not produce logically sound results, although the code did compile. It simply was listing all the rows rather than grouping them into Cities like I had asked it to.
I have looked for solutions on other forums as well as this one, however everyone was getting their question solved by simply adding an aggregate function ahead of their troublesome column names. When I did this, the SUM function wasn't working properly for specifically 'functional_amt'. If I remove it from the code and leave just 'total_chargs' and order by it, then the code compiles and is logical. However, I kind of need the 'functional_amt' column for my report haha.
Any help or advice would be much appreciated.
I just started learning SQL to extract data for my summer job last week and this is my first time posting here so sorry for any mistakes.
May 18, 2017 at 9:56 am
Provide the table DDL and insert script for some sample data please.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 18, 2017 at 10:00 am
It's the reference to the pre-aggregation functional_amt in the ORDER BY that is the issue.
You'll need to order by the aggregated value (likely what you intended anyway).
Like this:
select tlorder.destcity as "Destination", count(*) as "Number of Orders", sum(total_charges), sum(order_interliner.functional_amt) as "Total Partner Costs"
from tlorder, order_interliner
where tlorder.detail_line_id = order_interliner.detail_line_id
and destprov = 'ON'-- or origprov = 'OH'
and origcity = 'COLUMBUS'
and created_time >= '2017-4-1'
group by destcity
order by SUM(order_interliner.functional_amt) --Changed this
May 18, 2017 at 10:14 am
Jacob Wilkins - Thursday, May 18, 2017 10:00 AMIt's the reference to the pre-aggregation functional_amt in the ORDER BY that is the issue.You'll need to order by the aggregated value (likely what you intended anyway).
Like this:
select tlorder.destcity as "Destination", count(*) as "Number of Orders", sum(total_charges), sum(order_interliner.functional_amt) as "Total Partner Costs"
from tlorder, order_interliner
where tlorder.detail_line_id = order_interliner.detail_line_idand destprov = 'ON'-- or origprov = 'OH'
and origcity = 'COLUMBUS'
and created_time >= '2017-4-1'group by destcity
order by SUM(order_interliner.functional_amt) --Changed this
I'd also suggest using a proper JOIN, rather than resolving in your WHERE clause.SELECT tlorder.destcity AS [Destination],
COUNT(*) AS [Number of Orders],
SUM(total_charges) AS [Unnamed Column],
SUM(order_interliner.functional_amt) AS [Total Partner Costs]
FROM tlorder
JOIN order_interliner ON tlorder.detail_line_id = order_interliner.detail_line_id
WHERE destprov = 'ON'-- or origprov = 'OH'
AND origcity = 'COLUMBUS'
AND created_time >= '2017-4-1'
GROUP BY Destcity
ORDER BY SUM(order_interliner.functional_amt); --Changed this
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2017 at 10:16 am
Thanks to you both for the quick responses 🙂
Cheers Jacob Wilkins, that produced results exactly how I needed them.
Such a simple fix, I knew it was going to be something small haha, just couldn't find it myself.
May 18, 2017 at 10:19 am
Thom A - Thursday, May 18, 2017 10:14 AMJacob Wilkins - Thursday, May 18, 2017 10:00 AMIt's the reference to the pre-aggregation functional_amt in the ORDER BY that is the issue.You'll need to order by the aggregated value (likely what you intended anyway).
Like this:
select tlorder.destcity as "Destination", count(*) as "Number of Orders", sum(total_charges), sum(order_interliner.functional_amt) as "Total Partner Costs"
from tlorder, order_interliner
where tlorder.detail_line_id = order_interliner.detail_line_idand destprov = 'ON'-- or origprov = 'OH'
and origcity = 'COLUMBUS'
and created_time >= '2017-4-1'group by destcity
order by SUM(order_interliner.functional_amt) --Changed thisI'd also suggest using a proper JOIN, rather than resolving in your WHERE clause.
SELECT tlorder.destcity AS [Destination],
COUNT(*) AS [Number of Orders],
SUM(total_charges) AS [Unnamed Column],
SUM(order_interliner.functional_amt) AS [Total Partner Costs]
FROM tlorder
JOIN order_interliner ON tlorder.detail_line_id = order_interliner.detail_line_id
WHERE destprov = 'ON'-- or origprov = 'OH'
AND origcity = 'COLUMBUS'
AND created_time >= '2017-4-1'
GROUP BY Destcity
ORDER BY SUM(order_interliner.functional_amt); --Changed this
Thanks for the advice Thom.
I've started watching JOIN videos/tutorials to make my code more efficient and precise, as I have learned there is a difference, although the output is the same. I've just found SELECT and WHERE clauses have been easier to implement quickly as my job isn't directly related to SQL, just useful to learn when I have the time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply