CODEWITHSUNDEEP
sqlselect
Simon Balling
Simon Balling

Reputation: 511

Specific where for multiple selects

I have the following problem:

I have a table that looks something like this:

ArticleID|Group|Price
1|a|10
2|b|2
3|a|3
4|b|5
5|c|5
6|f|7
7|c|8
8|x|3

Now im trying to get a result like this:

PriceA|PriceRest
13|30

Meaning I want to sum all prices from group a in one column and the sum of everything else in another column.

Something like this doesnt work.

select 
    sum(Price) as PriceGroupA
    sum(Price) as PriceRest
from
    Table
where
    Group='a'
    Group<>'a'

Is there a way to achieve this functionality?

Upvotes: 1

Views: 42

Answers (3)

glaeran
glaeran

Reputation: 426

SELECT
sum(case when [Group] = 'a' then Price else 0 end) as PriceA,
sum(case when [Group] <> 'a' then Price else 0 end) as PriceRest
from
Table

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460168

You just need two sub-queries:

SELECT (SELECT SUM(PRICE)
        FROM Table1 
        WHERE [Group] ='a') AS PriceGroupA,
       (SELECT SUM(PRICE)
        FROM Table1 
        WHERE [Group]<>'a') AS PriceRest

Demo-Fiddle

Upvotes: 0

TechDo
TechDo

Reputation: 18649

Please try:

select
    sum(case when [Group]='A' then Price end) PriceA,
    sum(case when [Group]<>'A' then Price end) PriceRest
from
    Table

SQL Fiddle Demo

Upvotes: 1

Related Questions