Summing rows from the same table with different criterial

[aalnaif]

Hello!

I'm just posting to know if you can run a select statement where each column you're looking for could have different criteria? And if so, what's the syntax for the statement.

The pseudocode for what I'm looking for is like

select COLUMN1, COLUMN2 from TABLE1 as A, TABLE1 as B where
{CRITERIA for COLUMN1}
{CRITERIA for COLUMN2 that is different than CRITERIA from COLUMN1 but doesn't apply to COLUMN1};

The only other way I can think to do this is to run 2 separate queries, and then join them, but this involves creating 2 temp tables to store those queries.

Is there a way to do what I'm asking or atleast a more efficient way?

Thanks.

 

[PHV]

Any chance you could CLEARLY explain which result you want with which data ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aalnaif]

My raw data can be split into 2 subsets based on where the data originated. I want to be able to sum certain fields from the data based on which subset they are in, and then display them on a single table.
Take, for example these numbers based on theoretical sales.

Emp# Day Shift Amount
1 1/1 1 10
4 1/2 2 10
6 1/3 3 10
3 1/1 1 10
5 1/2 2 10
2 1/3 3 10

And I want to be able to state in one table

Day Shift1Sales Shift2Sales
1/1 20 20
1/2 20 20
1/3 20 20

 

[aalnaif]

Sorry, I'm also using MySQL 5.0.something if that helps.

 

[PHV]

You don't need multiple instances of the same table.
Have a look at the the CASE ... WHEN ... END statement.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886