SQL Grouping Syntax

J

JbL

Hi,

I am trying to run the below query that selects a few fields from a table -
Date, Sect, and DlyReturn where it basically computes the average return each
for each company in the sector. That part is easy I know.

Then I'm trying to add a few add'l companies to incorporate into the average
calculation which is what I can't get to group properly by the date and calc
the daily average with the combined entries.

I can only get the listing of each company and it's return, not the average
of all of the comps in a sector AND select add'l comps that fall into another
sector

SELECT [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) AS
AvgOfDlyReturn
FROM DailyData
GROUP BY [DailyData].Date, [DailyData].Sect, [DailyData].Cusip
HAVING ((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Sect)=10)) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="039380100")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="704549104")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="576206106"))
ORDER BY [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) DESC;


Thank you!!
 
D

Duane Hookom

I would take all the HAVING stuff and put it into a WHERE clause prior to
the GROUP BY.
Try this:

SELECT [Date], Sect, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
WHERE [Date]>#4/1/2005# AND (Sect=10 OR
Cusip IN ("039380100","704549104","576206106"))
GROUP BY [Date], Sect, Cusip
ORDER BY [Date], Sect;
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need the company ID/name in the SELECT clause to get an average on
the company per sector. Also, you don't need the [Date] since the
result will include ALL dates & you stated you only wanted sector and
company.

You might want to try this:

SELECT Sect, company, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
WHERE [Date]>#4/1/2005#
AND Sect=10
AND Cusip IN ("039380100", "704549104", "576206106")
GROUP BY Sect, company
ORDER BY Avg(DlyReturn) DESC;

Don't use the word "Date" as a column name - it is an SQL keyword, and,
it isn't very descriptive: What date is it: Sales date? Post date?,
Report Date?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAXyIechKqOuFEgEQKiygCglJVl2c3oWofLA2T6CRszwFCJSP4An2/4
S0/OmWHqnGGAOfLD8+4hQfmE
=WaZF
-----END PGP SIGNATURE-----
 
J

JbL

Thank you both Duane and MG for your responses. As I re-read my question I
don't think I posted it correctly since I was too eager to get it out there.

In this daily data table I have approx 1000 companies for each day. Each of
these companies has several potential grouping possibilities (sector,
industry, index, growth/value etc...) Normally, it would be easy to get the
daily average return for any of these "groups" by something like the
following:

SELECT Date,Sect, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
GROUP BY Date, Sect
HAVING (((Date)>#4/1/2005#) AND ((Sect)=10))
ORDER BY Date;


giving me something like

Date Sect AvgofDlyReturn
4/4/05 10 -.045
4/5/05 10 -1.03
4/6/05 10 .66
etc.......

by substituting a particular sector # with an another or using index # or I
combine the two I know how to get it to work if I'm selecting all of the
companies that within that grouping label. If I want to get all particlar
daily returns for all the groups (10, 20, 30, 40, etc) by removing the "AND
SECT=10" portion.

What I was trying to figure out yesterday is how can I get a daily average
return for a grouping level (say SECT=10) and included in that average
additional companies where I might use the cusip as the identifer. So if
Sector 10 has 10 companies each day and I want to compute an average of those
10 and 3 additional companies the daily average that gets returned will be
for all 13 companies, not the average of the 10 on 1 line and the 3 other on
another line.

I hope I'm not making this too confusing. I'm trying to move quickly up the
learning curve. Thank you very much.

PS: Thanks for heads-up on the DATE reserved word. I'll make some table
modifications so that won't be an issue.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need the company ID/name in the SELECT clause to get an average on
the company per sector. Also, you don't need the [Date] since the
result will include ALL dates & you stated you only wanted sector and
company.

You might want to try this:

SELECT Sect, company, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
WHERE [Date]>#4/1/2005#
AND Sect=10
AND Cusip IN ("039380100", "704549104", "576206106")
GROUP BY Sect, company
ORDER BY Avg(DlyReturn) DESC;

Don't use the word "Date" as a column name - it is an SQL keyword, and,
it isn't very descriptive: What date is it: Sales date? Post date?,
Report Date?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAXyIechKqOuFEgEQKiygCglJVl2c3oWofLA2T6CRszwFCJSP4An2/4
S0/OmWHqnGGAOfLD8+4hQfmE
=WaZF
-----END PGP SIGNATURE-----

Hi,

I am trying to run the below query that selects a few fields from a table -
Date, Sect, and DlyReturn where it basically computes the average return each
for each company in the sector. That part is easy I know.

Then I'm trying to add a few add'l companies to incorporate into the average
calculation which is what I can't get to group properly by the date and calc
the daily average with the combined entries.

I can only get the listing of each company and it's return, not the average
of all of the comps in a sector AND select add'l comps that fall into another
sector

SELECT [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) AS
AvgOfDlyReturn
FROM DailyData
GROUP BY [DailyData].Date, [DailyData].Sect, [DailyData].Cusip
HAVING ((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Sect)=10)) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="039380100")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="704549104")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="576206106"))
ORDER BY [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) DESC;
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Pardon me for jumping in.

To do what you want, you will have to leave the Sect field out of the results
and not group by it. SO you would need to take Duane's advice and move the Sect
and your additional comp criteria into a where clause and drop then from the
having clause.

Modifying Duane's original suggestion:

SELECT [Date], Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
WHERE [Date]>#4/1/2005# AND (Sect=10 OR
Cusip IN ("039380100","704549104","576206106"))
GROUP BY [Date]
ORDER BY [Date]

If you are using the query grid to build your query change the "Group By" under
Sect and Cusip to "WHERE"

Thank you both Duane and MG for your responses. As I re-read my question I
don't think I posted it correctly since I was too eager to get it out there.

In this daily data table I have approx 1000 companies for each day. Each of
these companies has several potential grouping possibilities (sector,
industry, index, growth/value etc...) Normally, it would be easy to get the
daily average return for any of these "groups" by something like the
following:

SELECT Date,Sect, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
GROUP BY Date, Sect
HAVING (((Date)>#4/1/2005#) AND ((Sect)=10))
ORDER BY Date;

giving me something like

Date Sect AvgofDlyReturn
4/4/05 10 -.045
4/5/05 10 -1.03
4/6/05 10 .66
etc.......

by substituting a particular sector # with an another or using index # or I
combine the two I know how to get it to work if I'm selecting all of the
companies that within that grouping label. If I want to get all particlar
daily returns for all the groups (10, 20, 30, 40, etc) by removing the "AND
SECT=10" portion.

What I was trying to figure out yesterday is how can I get a daily average
return for a grouping level (say SECT=10) and included in that average
additional companies where I might use the cusip as the identifer. So if
Sector 10 has 10 companies each day and I want to compute an average of those
10 and 3 additional companies the daily average that gets returned will be
for all 13 companies, not the average of the 10 on 1 line and the 3 other on
another line.

I hope I'm not making this too confusing. I'm trying to move quickly up the
learning curve. Thank you very much.

PS: Thanks for heads-up on the DATE reserved word. I'll make some table
modifications so that won't be an issue.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need the company ID/name in the SELECT clause to get an average on
the company per sector. Also, you don't need the [Date] since the
result will include ALL dates & you stated you only wanted sector and
company.

You might want to try this:

SELECT Sect, company, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
WHERE [Date]>#4/1/2005#
AND Sect=10
AND Cusip IN ("039380100", "704549104", "576206106")
GROUP BY Sect, company
ORDER BY Avg(DlyReturn) DESC;

Don't use the word "Date" as a column name - it is an SQL keyword, and,
it isn't very descriptive: What date is it: Sales date? Post date?,
Report Date?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAXyIechKqOuFEgEQKiygCglJVl2c3oWofLA2T6CRszwFCJSP4An2/4
S0/OmWHqnGGAOfLD8+4hQfmE
=WaZF
-----END PGP SIGNATURE-----

Hi,

I am trying to run the below query that selects a few fields from a table -
Date, Sect, and DlyReturn where it basically computes the average return each
for each company in the sector. That part is easy I know.

Then I'm trying to add a few add'l companies to incorporate into the average
calculation which is what I can't get to group properly by the date and calc
the daily average with the combined entries.

I can only get the listing of each company and it's return, not the average
of all of the comps in a sector AND select add'l comps that fall into another
sector

SELECT [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) AS
AvgOfDlyReturn
FROM DailyData
GROUP BY [DailyData].Date, [DailyData].Sect, [DailyData].Cusip
HAVING ((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Sect)=10)) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="039380100")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="704549104")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="576206106"))
ORDER BY [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) DESC;
Click to expand...
Click to expand...
Click to expand...
 
J

JbL

John,

Your slight change of Duane's code works as requested. I can't thank all of
you enough for helping people like me learn more about the applications and
move up the learning curve. Thanks again!!!!

Regards,

jbl





John Spencer (MVP) said:
Pardon me for jumping in.

To do what you want, you will have to leave the Sect field out of the results
and not group by it. SO you would need to take Duane's advice and move the Sect
and your additional comp criteria into a where clause and drop then from the
having clause.

Modifying Duane's original suggestion:

SELECT [Date], Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
WHERE [Date]>#4/1/2005# AND (Sect=10 OR
Cusip IN ("039380100","704549104","576206106"))
GROUP BY [Date]
ORDER BY [Date]

If you are using the query grid to build your query change the "Group By" under
Sect and Cusip to "WHERE"

Thank you both Duane and MG for your responses. As I re-read my question I
don't think I posted it correctly since I was too eager to get it out there.

In this daily data table I have approx 1000 companies for each day. Each of
these companies has several potential grouping possibilities (sector,
industry, index, growth/value etc...) Normally, it would be easy to get the
daily average return for any of these "groups" by something like the
following:

SELECT Date,Sect, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
GROUP BY Date, Sect
HAVING (((Date)>#4/1/2005#) AND ((Sect)=10))
ORDER BY Date;

giving me something like

Date Sect AvgofDlyReturn
4/4/05 10 -.045
4/5/05 10 -1.03
4/6/05 10 .66
etc.......

by substituting a particular sector # with an another or using index # or I
combine the two I know how to get it to work if I'm selecting all of the
companies that within that grouping label. If I want to get all particlar
daily returns for all the groups (10, 20, 30, 40, etc) by removing the "AND
SECT=10" portion.

What I was trying to figure out yesterday is how can I get a daily average
return for a grouping level (say SECT=10) and included in that average
additional companies where I might use the cusip as the identifer. So if
Sector 10 has 10 companies each day and I want to compute an average of those
10 and 3 additional companies the daily average that gets returned will be
for all 13 companies, not the average of the 10 on 1 line and the 3 other on
another line.

I hope I'm not making this too confusing. I'm trying to move quickly up the
learning curve. Thank you very much.

PS: Thanks for heads-up on the DATE reserved word. I'll make some table
modifications so that won't be an issue.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need the company ID/name in the SELECT clause to get an average on
the company per sector. Also, you don't need the [Date] since the
result will include ALL dates & you stated you only wanted sector and
company.

You might want to try this:

SELECT Sect, company, Avg(DlyReturn) AS AvgOfDlyReturn
FROM DailyData
WHERE [Date]>#4/1/2005#
AND Sect=10
AND Cusip IN ("039380100", "704549104", "576206106")
GROUP BY Sect, company
ORDER BY Avg(DlyReturn) DESC;

Don't use the word "Date" as a column name - it is an SQL keyword, and,
it isn't very descriptive: What date is it: Sales date? Post date?,
Report Date?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAXyIechKqOuFEgEQKiygCglJVl2c3oWofLA2T6CRszwFCJSP4An2/4
S0/OmWHqnGGAOfLD8+4hQfmE
=WaZF
-----END PGP SIGNATURE-----


JbL wrote:
Hi,

I am trying to run the below query that selects a few fields from a table -
Date, Sect, and DlyReturn where it basically computes the average return each
for each company in the sector. That part is easy I know.

Then I'm trying to add a few add'l companies to incorporate into the average
calculation which is what I can't get to group properly by the date and calc
the daily average with the combined entries.

I can only get the listing of each company and it's return, not the average
of all of the comps in a sector AND select add'l comps that fall into another
sector

SELECT [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) AS
AvgOfDlyReturn
FROM DailyData
GROUP BY [DailyData].Date, [DailyData].Sect, [DailyData].Cusip
HAVING ((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Sect)=10)) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="039380100")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="704549104")) OR
((([DailyData].Date)>#4/1/2005#) AND (([DailyData].Cusip)="576206106"))
ORDER BY [DailyData].Date, [DailyData].Sect, Avg([DailyData].DlyReturn) DESC;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Top x of A used in Avg of B 3
Copy data matching date and retain information in a monhtly report 1
Lookup problem Excel capabilities 4
Average 7
minimize repeating query criteria & generating monthly YTD totals 1
Import Access Choose function into Excel spreadsheet - issues! 0
Help importing Access Choose function into excel. 1
Query to select individual records 4

Top