Multiple GROUP BY in a SELECT

[Xmas]

Hi

I am trying to do something simple but failing. I am trying to get
aggregate data with different conditions in a SQL query.

That is, with a table date,fooA,fooB, I want to output rows containing
year, average fooA (where fooB=1) average fooA (where fooB=2) average
fooA (where fooB=3) group by year(date)

The tests I need on fooB are rather more complex but this is essentially
what I am after, but can't break it down into a single query. UNION
merges rows from different queries and I want to merge columns!

 

[Dirk Goldgar]

Hi

I am trying to do something simple but failing. I am trying to get
aggregate data with different conditions in a SQL query.

That is, with a table date,fooA,fooB, I want to output rows containing
year, average fooA (where fooB=1) average fooA (where fooB=2) average
fooA (where fooB=3) group by year(date)

The tests I need on fooB are rather more complex but this is essentially
what I am after, but can't break it down into a single query. UNION
merges rows from different queries and I want to merge columns!

Maybe I'm not understanding you correctly, but it seems you want something
like:

SELECT
Year(DateField),
FooB,
Avg(fooA) As AvgFooA
FROM YourTable
GROUP BY
Year(DateField),
FooB;

 

[Xmas]

Maybe I'm not understanding you correctly, but it seems you want something
like:

SELECT
Year(DateField),
FooB,
Avg(fooA) As AvgFooA
FROM YourTable
GROUP BY
Year(DateField),
FooB;

Hello Dirk, you are understanding correctly but unfortunately I used
that just as an example, sadly the tests I am doing on FooB are rather
more complex; they are actually different categorisations of people
based on their age, sex, and other bits and pieces.

I would have thought a join would do it (since I am trying essentially
to merge columns of data grouped by year(datefield) on the year) but I
am having no luck.

 

[Sylvain Lafontaine]

First, if you question is about queries in MS-Access/JET, you shouldn't post
in the newsgroup m.p.access.adp.sqlserver as the syntax for SQL-Server
queries is different from the syntax of Access/JET queries and the examples
that I could give you won't work in MS-Access. Another newsgroup such
m.p.access.queries would has been a more appropriate newsgroup for your
question.

Second, for your question, it looks like that you want to have some pivot
table but it's not very clear from your post. You should repost in
m.p.access.queries but with some examples of datas and of results so that
people can understand what you want exactly.

Finally, you can merge columns using an Union query if you put zero for
blank columns, put the Union in a subquery and use the Sum() function in the
global query to get back on your feet. Another possibility would be to use
a mixture of subqueries and of JOIN or of Left Join. Another, simple
solution would be to simply use a temporary table to compute and store the
intermediate results.

Access/JET has special instructions for Pivoting but you should ask in a
newsgroup dedicated to JET/Access if you want an answer for that.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Xmas]

First, if you question is about queries in MS-Access/JET, you shouldn't post
in the newsgroup m.p.access.adp.sqlserver as the syntax for SQL-Server
queries is different from the syntax of Access/JET queries and the examples
that I could give you won't work in MS-Access. Another newsgroup such
m.p.access.queries would has been a more appropriate newsgroup for your
question.

Thank you - my apologies, I'll repost in the correct place.

 

[John Spencer]

One method would be:

SELECT Year([Date]) as TheYear
, Avg(IIF(Foob=1,FooA,Null)) as A1Average
, Avg(IIF(Foob=2,FooA,Null)) as A2Average
, Avg(IIF(Foob=3,FooA,Null)) as A3Average
FROM SomeTable
GROUP BY Year([Date])

That will return the averages based on FooB's value for each year in one row.
The same thing could be accomplished with a crosstab query.

If your expressions are more complex, you might be able to handle them in the
same manner or using other query structures (sub-queries in the SELECT clause
and / or sub-queries in the FROM clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County