Grouping Select Statements with where clause

Hello

What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.

campaign Col1 Col2

<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>No you don't. You just think you do. What you really want to do is create a CROSSTAB query. Look it up in Books Online.sql

Grouped information from two tables

Hi,

I have two statements which when I join by a union statement give the folowing:

2005 11 0.000000
2005 12 0.000000
2006 1 0.000000
2006 1 50813.058500
2006 10 0.000000
2006 11 0.000000
2006 12 0.000000
2006 12 63224.511250
2006 2 0.000000
2006 2 59164.234500
2006 3 0.000000
2006 4 0.000000
2006 5 0.000000
2006 6 0.000000
2006 6 82442.570750
2006 7 0.000000
2006 7 61809.497750
2006 8 0.000000
2006 9 0.000000
2007 1 0.000000
2007 2 0.000000
2007 3 0.000000
2007 4 0.000000
2007 5 0.000000
2007 6 0.000000
2007 7 0.000000
2007 8 0.000000

What I want is to merge the values

2006 1 0.000000
2006 1 50813.058500

into one row, the months with zero figures are required.

Thanks

Not the best of descriptions, but I have now solved this problem.

Answer = Ist table joins to second table (Select statement) via a right outer join.

sql

Group by and Case Statements

Hi,
I have a query where I am using a case statement and a group by e.g.
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3, Field4
The problem I am having is the group by isn't using the value from
the Case Statement, it's not grouping the records correctly. Anyone
have any idea's why that would happen?
Thanks for the helpBy definition, the expressions in the GROUP BY clause reffer to the
columns from the table, not to the columns defined in the SELECT
clause. You probably need to use a subquery for this:
SELECT Field1, Field2, Field3, MAX(Field4), TheExpression as Field4
FROM (
SELECT Field1, Field2, Field3, Field4,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as TheExpression
FROM <your table>
WHERE <something>
) x
GROUP BY Field1, Field2, Field3, TheExpression
Razvan|||Thanks, that's what I figured. I was hoping there was a more eligant
way around that.
Thanks|||Simply copy the same CASE expression used in the SELECT list into the
GROUP BY:
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END
Roy Harvey
Beacon Falls, CT
On 3 May 2006 09:25:44 -0700, "Red2" <sdibello@.gmail.com> wrote:

>Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
>the Case Statement, it's not grouping the records correctly. Anyone
>have any idea's why that would happen?
>Thanks for the help|||Red2 wrote:
> Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
> the Case Statement, it's not grouping the records correctly. Anyone
> have any idea's why that would happen?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It'd be better if you showed the actual query. Your example has a
Max(Field4) and the CASE expression is labeled as Field4. If the GROUP
BY clause has Field4 is will be grouping the Field4 in the Max()
function. Obviously, not what you want to do. Is this a misnomer or
are you really using the same column in the Max() function and as the
CASE expression's name?
If you want to GROUP BY the result of a CASE expression you have to
include the CASE expression in the GROUP BY clause:
GROUP BY field1, field2, field3, CASE WHEN <something> THEN X ELSE Y END
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRFkUgYechKqOuFEgEQLZ2ACdHDbb0StLG/MEkaOpME4KfeDYTfcAn1I2
EEZOhQ/brevVe0gSJNujPhER
=aD2k
--END PGP SIGNATURE--