Problem with select queries 1

[morfasie]

I have a query like this:
SELECT sum( Premium ) AS soweto
FROM production
WHERE `Agent Code`
LIKE '%GB01%'
UNION
SELECT sum( `Premium` ) AS `NW meretele`
FROM production
WHERE `Agent Code`
LIKE '%WW02%'

however it returns the results in one column, I want to return in two columns with the one column being 'Soweto' and the next column being 'NW meretele'

thanks

 

[Forri]

Thats the purpose of unions my friends...don't know how it didn't give you an error in the first place!

But if you want the above to work you should do something like:

SELECT sum(P.Premium)AS soweto, sum(T.Premium)AS `NW meretele`
FROM production P, production T
WHERE `P.Agent Code` LIKE '%GB01%' and T.Agent Code`
LIKE '%WW02%'

or something similar.....

You could check out joins!


Nick

 

[morfasie]

Hi thanks for the reply, how would I use it with a left join? do you knowe of any usefull sites about joins in sql??

 

[JarlH]

How come you want to use a left join?

(You don't know the difference between a union and a join, but you know you want a left join...)

 

[Golom]

Try this
[blue][tt]
SELECT sum( Premium ) AS soweto, 0 As `NW meretele`
FROM production
WHERE `Agent Code`
LIKE '%GB01%'
UNION
SELECT 0 , sum(Premium)
FROM production
WHERE `Agent Code`
LIKE '%WW02%'
[/tt][/blue]