Miguel Bérniz Miguel Bérniz - 16 days ago 5
SQL Question

Sum of counts on multiple tables MSSQL query

I have the following query.

SELECT (
SELECT
COUNT (fibre_circuit.data_id)
FROM fibre_circuit
INNER JOIN bus_std_iptv
ON bus_std_iptv.d_parent_id = fibre_circuit.data_id
where fibre_circuit.data_id = 778
) AS Count1,
(
SELECT
COUNT (fibre_circuit.data_id)
FROM fibre_circuit
INNER JOIN bus_pre_iptv
ON bus_pre_iptv.d_parent_id = fibre_circuit.data_id
where fibre_circuit.data_id = 778
) AS Count2,
(
SELECT
COUNT (fibre_circuit.data_id)
FROM
fibre_circuit
INNER JOIN iptv
ON iptv.d_parent_id = fibre_circuit.data_id
where fibre_circuit.data_id = 778
) AS Count3,
(
SELECT DISTINCT
fibre_circuit.data_id
FROM fibre_circuit
INNER JOIN bus_std_iptv
ON bus_std_iptv.d_parent_id = fibre_circuit.data_id
where fibre_circuit.data_id = 778
)
AS circuit


The result is:

Count 1 | Count 2 | Count 3 | Circuit

3 | 1 | 1 | 778


I need to SUM Count1 + Count2 + Count3 to just receive:

Total | Circuit

5 | 778


Anyone could help on elaborating the right query to get desired result?

Thanks in advance,

Miguel

C B C B
Answer

Try This:

SELECT  (  
    SELECT
    COUNT (fibre_circuit.data_id)   
    FROM
    fibre_circuit  
      INNER JOIN
    bus_std_iptv  
    ON
    bus_std_iptv.d_parent_id = fibre_circuit.data_id  
    where
    fibre_circuit.data_id = 778
    )  +  
    (SELECT
    COUNT (fibre_circuit.data_id)  
    FROM
    fibre_circuit  
    INNER JOIN
    bus_pre_iptv  
    ON
    bus_pre_iptv.d_parent_id = fibre_circuit.data_id  
    where
    fibre_circuit.data_id = 778
    ) +  
    (
    SELECT
    COUNT (fibre_circuit.data_id)  
    FROM
    fibre_circuit  
    INNER JOIN
    iptv  
    ON
    iptv.d_parent_id = fibre_circuit.data_id  
    where
    fibre_circuit.data_id = 778  
    ) AS Total,  
    (
    SELECT DISTINCT
    fibre_circuit.data_id  
    FROM
    fibre_circuit  
    INNER JOIN
    bus_std_iptv  
    ON
    bus_std_iptv.d_parent_id = fibre_circuit.data_id  
    where
    fibre_circuit.data_id = 778  
    )
    AS circuit