Adding Columns to the output of a Group By Query.

Adding Columns to the output of a Group By Query.

Post by Zed » Thu, 15 Feb 2001 07:02:56



Hi Guys

The situation is this -

The application is a call center application.  The query retrieves the
oldest Service Request from Countries in a Particular Financial Month.

I have a select query similar to the one below:

Select min(createdate), Country, FinancialMonth
From Tablename
Group By
Country,
FinancialMonth

I am aware that I cannot include any field in this query that is not
included in the group by clause or used with an aggregate function.
However, I would like to
include a few more columns in the final output of this query.   I can't
group on the extra columns I want to add because they then alter the results
of the query increasing the number of rows significantly.
I have tried looking for a common column I could join the results of the
query on and maybe, a temporary table containing the other columns but none
of the columns in the "group by" query can guarantee uniqueness.

Any Ideas Guys on how I could include the extra columns in the final output
of my query?

Would really appreciate some help!

Thanks

ZED

 
 
 

Adding Columns to the output of a Group By Query.

Post by Marcel » Thu, 15 Feb 2001 07:12:08


Did you try something like...
Select
    min(createdate),
    Country,
    FinancialMonth,
    NULL as ExtraColumn1,
    NULL as ExtraColumn2
From Tablename
Group By
Country,
FinancialMonth

This will add 2 extra columns to your table named  ExtraColumn1 and
ExtraColumn2 whose values are NULL.


Quote:> Hi Guys

> The situation is this -

> The application is a call center application.  The query retrieves the
> oldest Service Request from Countries in a Particular Financial Month.

> I have a select query similar to the one below:

> Select min(createdate), Country, FinancialMonth
> From Tablename
> Group By
> Country,
> FinancialMonth

> I am aware that I cannot include any field in this query that is not
> included in the group by clause or used with an aggregate function.
> However, I would like to
> include a few more columns in the final output of this query.   I can't
> group on the extra columns I want to add because they then alter the
results
> of the query increasing the number of rows significantly.
> I have tried looking for a common column I could join the results of the
> query on and maybe, a temporary table containing the other columns but
none
> of the columns in the "group by" query can guarantee uniqueness.

> Any Ideas Guys on how I could include the extra columns in the final
output
> of my query?

> Would really appreciate some help!

> Thanks

> ZED


 
 
 

Adding Columns to the output of a Group By Query.

Post by Steve Dassi » Thu, 15 Feb 2001 07:48:38


I hate nit pickers but just curious why you put extra columns in
group by and not select.Select restrictions in group by queries are for
table columns not
constants or expressions/functions involving constants.

Stevie
www.angelfire.com/ny4/rac/

 
 
 

Adding Columns to the output of a Group By Query.

Post by Zed » Thu, 15 Feb 2001 09:15:34


Hi

I have probably been misunderstood.  

I do not intend to add any more columns to the group by clause itself.  I would like to include a few more table columns to the select clause but I would neither like to include these columns in the group by clause or use them with an aggregate function.

This means unfortunately that I have to find some other way of getting my columns into the query output and that basically, was my question.

Thanks for replying

Zed

*** Sent via Developersdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Adding Columns to the output of a Group By Query.

Post by Greg Wietin » Thu, 15 Feb 2001 09:39:04


I can probably help you construct a syntax for what you are trying to
accomplish. But you need to provide what it is you are trying to do.
Providing some sample code would be very helpful to us.

Greg


Quote:

> Hi

> I have probably been misunderstood.

> I do not intend to add any more columns to the group by clause itself.  I

would like to include a few more table columns to the select clause but I
would neither like to include these columns in the group by clause or use
them with an aggregate function.
Quote:

> This means unfortunately that I have to find some other way of getting my

columns into the query output and that basically, was my question.
Quote:

> Thanks for replying

> Zed

> *** Sent via Developersdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Adding Columns to the output of a Group By Query.

Post by BP Margoli » Thu, 15 Feb 2001 12:48:41


Zed,

Perhaps something on the order of ...

select t1.createdate, t1.Country, t1.FinancialMonth, ...
from Tablename as t1
join (select min(createdate) as createdate, Country, FinancialMonth
         from Tablename
         group by Country, FinancialMonth) as t2
  on (t1.Country = t2.Country  and
         t1.FinancialMonth = t2.FinancialMonth  and
         t1.createdate = t2.createdate)

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hi Guys

> The situation is this -

> The application is a call center application.  The query retrieves the
> oldest Service Request from Countries in a Particular Financial Month.

> I have a select query similar to the one below:

> Select min(createdate), Country, FinancialMonth
> From Tablename
> Group By
> Country,
> FinancialMonth

> I am aware that I cannot include any field in this query that is not
> included in the group by clause or used with an aggregate function.
> However, I would like to
> include a few more columns in the final output of this query.   I can't
> group on the extra columns I want to add because they then alter the
results
> of the query increasing the number of rows significantly.
> I have tried looking for a common column I could join the results of the
> query on and maybe, a temporary table containing the other columns but
none
> of the columns in the "group by" query can guarantee uniqueness.

> Any Ideas Guys on how I could include the extra columns in the final
output
> of my query?

> Would really appreciate some help!

> Thanks

> ZED

 
 
 

1. Adding Autonumber Style Column to Query Output

Hello - I'm pretty new to SQL server and have started writing reports and
generating queries which need to be exported. Are there any inate
functions/data types a la MS Access that allow you to give each record a
unique identifier (besides NEWID). Basically I'm trying to generate a column
that goes from 1 to N (N being the last record). Thanks,

Baris

2. Conversion from SQL-Server to Oracle

3. Include Group and Non group columns in same query

4. SQL

5. Adding a numbered column to SELECT output

6. problems while building sybPerl for DECstations

7. Adding columns to a table and adding length to columns

8. Adding Columns to the output of a Group By Query.

9. Adding rank to grouping query

10. Query Output - how to loose column headers?

11. Replacing text in query output (from a text-column)

12. Adding groups to groups