Calcul (multiplication) of two select-result

Question

I'm trying to multiply two numbers I got from a SELECT statement of a unique query. I want to get the number of providers and the number of proposals (the query I made displays that), and multiply both on the same line (that I can't do).

I've made a very simple example to show you (same code as below) : DEMO ON FIDDLE

Create 2 providers working on 2 departments :

CREATE TABLE ##Provider
(
  id INT,
  p_name VARCHAR(50),
  id_dep INT
)
INSERT INTO ##Provider (id, p_name, id_dep) VALUES 
    (1, 'toto', 10),
    (2, 'toto', 11),
    (3, 'tata', 9);

Create 4 proposal on 2 departments :

CREATE TABLE ##Proposal
(
  id INT,
  c_name VARCHAR(50),
  id_dep INT
)
INSERT INTO ##Proposal (id, c_name, id_dep) VALUES 
    (1, 'propA', 10),
    (2, 'propB', 09),
    (3, 'propC', 10),
    (4, 'propD', 10);

Create the department table :

CREATE TABLE ##Department
(
  id INT,
  d_name VARCHAR(50)
)
INSERT INTO ##Department (id, d_name) VALUES 
    (9, 'dep9')
    ,(10, 'dep10')
    ,(11, 'dep11');

Here I can display the number of providers and proposals by department (the real query is a lot more complex so I'd like to keep the 2 subrequests) :

    select 
        id,
        d_name,
        nb_provider = (
            SELECT COUNT(DISTINCT Id)
            FROM ##Provider p
            WHERE p.id_dep = dep.id
        ),
        nb_proposal = (
            SELECT COUNT(DISTINCT Id)
            FROM ##Proposal pp
            WHERE pp.id_dep = dep.id
        )
    from ##Department dep
    WHERE dep.id = 10

But I CAN'T display a calcul of those two number :

    select 
        id,
        d_name,
        nb_provider = (
            SELECT COUNT(DISTINCT Id)
            FROM ##Provider p
            WHERE p.id_dep = dep.id
        ),
        nb_proposal = (
            SELECT COUNT(DISTINCT Id)
            FROM ##Proposal pp
            WHERE pp.id_dep = dep.id
        ),
        calcul = (nb_provider * nb_proposal)  --> DOESN'T WORK
    from ##Department dep
    WHERE dep.id = 10

I haven't tried a lot because I am not sure if this is even possible... maybe should I use UNION ?

Answer 1

I would recommend lateral joins:

select 
    d.id,
    d.d_name,
    p.nb_provider,
    pp.nb_proposal
    (p.nb_provider * pp.nb_proposal) calcul
from ##department d
outer apply (
    select count(distinct id) nb_provider
    from ##provider p
    where p.id_dep = d.id
) p
outer apply (
    select count(distinct id) nb_proposal
    from ##proposal pp
    where pp.id_dep = d.id
) pp
where d.id = 10