CODEWITHSUNDEEP
sqlt-sqlunion
cja
cja

Reputation: 10026

How can I improve this union?

The following SQL does almost exactly what I want.

SELECT p.ProductNumber "Plan Number",
       p.Name,
       p.price         "Monthly Rate",
       count(*)        "Group",
       '0'             "Direct Debit"
FROM   contact c,
       product p
WHERE  c.integ_schemeid = p.ProductId
       AND c.ParentCustomerId IS NOT NULL
GROUP  BY p.ProductNumber,
          p.Name,
          p.price
UNION
SELECT p.ProductNumber "Plan Number",
       p.Name,
       p.price         "Monthly Rate",
       '0'             "Group",
       count(*)        "Direct Debit"
FROM   contact c,
       product p
WHERE  c.integ_schemeid = p.ProductId
       AND c.ParentCustomerId IS NULL
GROUP  BY p.ProductNumber,
          p.Name,
          p.price

With results:

enter image description here

I want one line for each plan, with group and direct debit values together in one row.

Is this possible in T-SQL?

Upvotes: 0

Views: 53

Answers (2)

xanatos
xanatos

Reputation: 111870

Try

select p.ProductNumber "Plan Number",p.Name,p.price "Monthly Rate",
    count(CASE WHEN c.ParentCustomerId IS NOT NULL THEN 1 END) "Group", 
    count(CASE WHEN c.ParentCustomerId IS NULL THEN 1 END) "Direct Debit"
from contact c,product p 
where c.integ_schemeid = p.ProductId
group by p.ProductNumber,p.Name,p.price

On failure of the CASE WHEN, the CASE will return NULL, and COUNT doesn't "count" NULL values. The 1 is a random value. It only means "not NULL". You could have used 'X', 'Foo', 0, -1...

Upvotes: 2

zxc
zxc

Reputation: 1526

OK This query assumes that each name has 2 rows so Ill use inner join

select t1.[Plan Number],t1.Name, t1.[Monthly Rate],t1.Group,t2.[Direct Debit] from
(
select p.ProductNumber "Plan Number",p.Name,p.price "Monthly Rate",count(*) "Group", '0' "Direct Debit"
from contact c,product p 
where c.integ_schemeid = p.ProductId
and c.ParentCustomerId is not null
group by p.ProductNumber,p.Name,p.price
)t1
inner join
(
select p.ProductNumber "Plan Number",p.Name,p.price "Monthly Rate", '0' "Group", count(*) "Direct Debit"
from contact c,product p 
where c.integ_schemeid = p.ProductId
and c.ParentCustomerId is null
group by p.ProductNumber,p.Name,p.price) t2
on t1.[Plan Number] = t2.[Plan Number] and t1.name=t2.name and t1.[Monthly Rate] = t2.[Monthly Rate]

On this query i use your same computation then join to the other table, you can modify the column you wanted to filter but for column group i used the upper queryand for the column direct-debit i used the lower query

Upvotes: 0

Related Questions