Multi-Select Statement with Multiple Conditions

[Scorp Scorp]

Helloo all,

in a previouse thread, and with a lot of help, the following table

------------------------------- 
Desc | Entity |  Name  | rate | qty 
--------------------------------- 
xxxx    1        Mark    100     1 
xxxx    2        Mark    100     2 
xxxx    1        Joe     100     3 
xxxx    2        Kive    100     4 
xxxx    1        Ben     100     5 
xxxx    1        Ben     100     6 
-----------------------------------

in an sql statement

TRANSFORM Sum([rate]*[qty]) AS Expr1  
SELECT TEST.name 
FROM TEST  
GROUP BY TEST.name 
ORDER BY TEST.Name DESC   
PIVOT TEST.entity;

to have the following result

========================== 
Names   | 1   |   2 
========================= 
MARK      100     0
KIVE       0     400
JOE       300     0
BEN       1100    0

Now custmization to be performed.
Lets say i have more entities.
What i need to get as result is

========================== 
Names   | 1   |   
========================= 
MARK      100     x
KIVE       0      y
JOE       300     z
BEN       1100    v

Where x , y , z , v are amounts of each guy in all other entities. ie: entities left, other than 1.

Any suggestions

[Rabbit]

You'll have to do an aggregate query for the other entities and then full outer join the original table filtering for entity 1 on the original table.

[Scorp Scorp]

Thanks Rabbit but i failed translating your words into SQL ....

I thought multi select will do the job but non.

Can you translate your idea into SQL ?

thnaks in advance

[Rabbit]

Please post the SQL string that you tried.

[Scorp Scorp]

TRANSFORM Sum([rate]*[qty]) AS Expr1
SELECT TEST.name, ([B][U]select SUM([rate[*[qty] From Test where[/U][/B]   [I]here i couldnt find what to put[/I]
FROM TEST
GROUP BY TEST.name
ORDER BY TEST.Name DESC
PIVOT TEST.entity;

I tried to put a condition , where entity <>1 ... but sure didnt work .


I tried to translate how i figure it how, but couldnt get to the right sql statment. also trying to put the conditions in diff places inside the sql, appeared all to be wrong .... cant figure out how to embbed two sqls to get the required results ....

Never thought of join (which u mentioned earlier) .

[Rabbit]

I don't understand why you have a subquery, I didn't mention a subquery in my post. You just need to take your current query and add a where.

Then you create a simple select query selecting only the entity 1.

Finally, you bring it all together with a full outer join.

[NeoPa]

Full OUTER JOINs are not available to Jet SQL Rabbit. Are you referring to non-joined tables (Cartesian Product), where the input sources are listed without JOINs (See SQL JOINs for clarification of terms)?

[Rabbit]

If jet SQL doesn't have full outer join, then yes, they will have to use a cross join with the join information in the where clause.

[Scorp Scorp]

Rabbit and Neopa , to me now you seem from Mars guys , am not that goooood in Databases , or at least up to that level to know wat you guys talking about ...

Didnt get it, and couldnt solve it ... butt thanks for your effort guys ... thanks.

Thanks for the article Neopa , got an idea on join , but unfortunately couldt relate it to my example here to come up with the prorper solution.

regards,

[NeoPa]

I never could work out what the question meant, so I just posted what I knew about when the concept of OUTER JOINs came up. Sorry I couldn't be more help.

[Rabbit]

First of all, when I said you need to put a where/having clause in your aggregate query, I don't know how you came up with this.

TRANSFORM Sum([rate]*[qty]) AS Expr1 
SELECT TEST.name, (select SUM([rate[*[qty] From Test where   here i couldnt find what to put 
FROM TEST 
GROUP BY TEST.name 
ORDER BY TEST.Name DESC 
PIVOT TEST.entity;

What you need is this.

SELECT TEST.name, Sum([rate]*[qty]) AS Expr1 
FROM TEST
WHERE TEST.entity <> 1 
GROUP BY TEST.name 
ORDER BY TEST.Name DESC;

Then you just need to use a select query to select the records with entity 1 and full outer join (or its equivalent in Jet SQL syntax) the results.

[Scorp Scorp]

Dear Rabbit ,

What you are providing here is not what i need .

By trial and error i managed find out what u have given me above .

also i know how to get the records for certain entity .

But this makes them 2 seperate queries ,i.e: two seperate sql statements .

Then you just need to use a select query to select the records with entity 1 and full outer join (or its equivalent in Jet SQL syntax) the results.

This is my problem .... what you are saying in english letters , i canot turn it to SQL alphabets ... i.e: in one single sql statment that i can set it in my report.

Thankss any way

[Rabbit]

If what I posted isn't what you need, then I don't know what it is you're looking for.

[gershwyn]

Where x , y , z , v are amounts of each guy in all other entities. ie: entities left, other than 1.

I've read this a couple times, and it's very hard to tell what you're asking. Are you saying that no matter how many entities there are, there should only be two columns: one for entity = 1, and a second for entity >= 2? On the off chance that is what you want, you can achieve it easily enough with your existing query. Just change the last line to:

PIVOT IIF([Entity] = 1, "1", "2+")

If that is the case though, your sample data does nothing to illustrate it. There are no entities higher than 2, so the results of this query will be the same as your original.

[Scorp Scorp]

@Rabbit, thanks for following up with me in this thread, and sorry if i couldnt address what i neeed properly ...

@gershwyn

what i have said
Where x , y , z , v are amounts of each guy in all other entities. ie: entities left, other than 1.

is exactly what i meant , my posted example is just a sample. I have more than one entity, but i posted it by mentioning just entity 1 and entity 2 just for breifing.

Your last line

 PIVOT IIF([Entity] = 1, "1", "2+")

Is just what i need, will get the sum of entity=1 in one colomn, and sum of all other entities in a second colomn no matter what the entity value...

Worked great ...

Awsome :)

thanks a lot guys

Thanks gershwyn ...