Group By with added Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By with added Select

Author

Topic

SQL greenie
Starting Member

2 Posts

Posted - 2008-10-29 : 15:13:08

Hello,

I am new to SQL, but working to learn quickly.

I have the following query I am writing, but am facing the nasty Group By rules. I read a related posting on this site that prompted me to join, but could use some help with the following.

I have grouped the following from a joined table, but want to also select and display a date field from the primary po_headers_all table, but it violates the Group By rules. How can I add other columns not involved in the group? Specifically, I want to add p.creation_date so that it displays with the corresponding p.segment1.

select p.segment1 as po_number, sum(decode(nvl(pl.list_price_per_unit,
0), 0, pl.unit_price, pl.list_price_per_unit) * pl.quantity) as total_amt
from po_headers_all p,
po_lines_all pl
where p.po_header_id = pl.po_header_id
and p.creation_date >= '01-JUL-2007'
and nvl(confirming_order_flag, 'N') = 'Y'
and nvl(p.cancel_flag, 'N') != 'Y'
and nvl(pl.cancel_flag, 'N') != 'Y'
group by p.segment1
order by total_amt;

Thank you.

SQL greenie

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-29 : 15:17:53

You can use max(p.creation_date) as creation_date for example.
But be sure that's what you want. There may be many other dates in your group...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-10-30 : 03:31:52

select p.segment1 as po_number, sum(decode(nvl(pl.list_price_per_unit,
0), 0, pl.unit_price, pl.list_price_per_unit) * pl.quantity) as total_amt, p.creation_date
from po_headers_all p,
po_lines_all pl
where p.po_header_id = pl.po_header_id
and p.creation_date >= '01-JUL-2007'
and nvl(confirming_order_flag, 'N') = 'Y'
and nvl(p.cancel_flag, 'N') != 'Y'
and nvl(pl.cancel_flag, 'N') != 'Y'
group by p.segment1, p.creation_Date
order by total_amt;

if there are multiple dates for p.creation_date, it will group them into subgroups. is it what you want?

check out my blog at http://www.aqauriumlore.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 03:41:41

show your sample data and output you expect

SQL greenie
Starting Member

2 Posts

Posted - 2008-11-04 : 09:39:37

Thank you, Webfred, EugeneLim11, and visakh16,

The max condition worked in this case. I also realized that if I wanted to be able to pull any additional columns outside of the aggregation, the max condition would negate it and I would need to restructure the query altogether. I have recently stumbled on a select construct nested within a from-where structure that I think may do the trick in the future restructuring.

Thank you for your help.

SQL greenie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources