# How to get a total from 3 different counts ? [Answered]RSS

## 9 replies

Last post Mar 19, 2015 11:48 AM by ricas

• ricas

Member

41 Points

409 Posts

### How to get a total from 3 different counts ?

Mar 18, 2015 06:02 PM|ricas|LINK

Hi,

I am using these 3 SQL queries to count the number of reports each instructor (inst) is submitting per day, during the last year / 360 days. The first query also limit the count so that no reports filed before 01.01.2015 is counted, as well as in some cases the counter will count 2 for one report.

I need to count a total of these 3 queries, but how do I combine them ? Any suggestions ?

Best regards

1.

WITH CTE_C AS (SELECT DISTINCT inst, opc, DATEDIFF(dd, 0, dato) AS DayNum, dato FROM opc WHERE (dato > DATEADD(DAY, - 365, GETDATE()))) SELECT inst, SUM(CASE WHEN opc IN ('A' , 'C' , 'E') THEN 2 ELSE 1 END) AS Total FROM CTE_C AS CTE_C_1 WHERE (dato > '01 - 01 - 2015') GROUP BY inst ORDER BY Total DESC

2.

SELECT COUNT(DISTINCT DATEDIFF(dd, 0, dato)) AS Total, inst FROM tting WHERE (dato > DATEADD(DAY, - 365, GETDATE())) GROUP BY inst ORDER BY Total DESC

3.

SELECT COUNT(DISTINCT DATEDIFF(dd, 0, dato)) AS Total, inst FROM cgrade WHERE (dato > DATEADD(DAY, - 365, GETDATE())) GROUP BY inst ORDER BY Total DESC

_________________________________________________

..... I am new to all this, so please be patient
• gimimex

Contributor

5460 Points

1123 Posts

### Re: How to get a total from 3 different counts ?

Mar 18, 2015 07:15 PM|gimimex|LINK

Hi,

Try:

```with
CTE_C AS
(
SELECT DISTINCT inst, opc, dato
FROM opc
WHERE dato > '01 - 01 - 2015' and dato > DATEADD(DAY, - 365, GETDATE())
),

CTE_Opc as
(
SELECT inst, SUM(CASE WHEN opc IN ('A' , 'C' , 'E') THEN 2 ELSE 1 END) AS Total
FROM CTE_C
GROUP BY inst
),

CTE_tting as
(
SELECT COUNT(DISTINCT DATEDIFF(dd, 0, dato)) AS Total, inst
FROM tting
WHERE dato > DATEADD(DAY, - 365, GETDATE())
GROUP BY inst
),

CTE_cgrade as
(
SELECT COUNT(DISTINCT DATEDIFF(dd, 0, dato)) AS Total, inst
FROM cgrade
WHERE dato > DATEADD(DAY, - 365, GETDATE())
GROUP BY inst
)

select
coalesce(o.inst, t.inst, c.inst) as inst,
o.Total as TotalOpc,
t.Total as TotalTting,
c.Total as TotalCgrade
from CTE_Opc as o
full join CTE_tting as t
on t.inst = o.inst
full join CTE_cgrade as c
on c.inst = o.inst or
c.inst = t.inst
```

Hope this helps.

• oned_gk

All-Star

52222 Points

15517 Posts

### Re: How to get a total from 3 different counts ?

Mar 18, 2015 08:58 PM|oned_gk|LINK

```SELECT COUNT(DayNum) AS Total, inst FROM
(
query1
UNION ALL
query2
UNION ALL
query3
) GROUP BY inst ORDER BY Total DESC```

make query1,2,3 returning daynum and inst

Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer
• ricas

Member

41 Points

409 Posts

### Re: How to get a total from 3 different counts ?

Mar 19, 2015 03:22 AM|ricas|LINK

Hi gimimex,

Thanks, the code works fine but returns a total count for each query - I need a total for all 3 ?

Best regards

_________________________________________________

..... I am new to all this, so please be patient
• ricas

Member

41 Points

409 Posts

### Re: How to get a total from 3 different counts ?

Mar 19, 2015 03:24 AM|ricas|LINK

Hi oned_gk

#### oned_gk

make query1,2,3 returning daynum and inst

....I am not sure how to do this, have tried several ways but no luck ?

Best regards

_________________________________________________

..... I am new to all this, so please be patient
• purplepint

Member

333 Points

374 Posts

### Re: How to get a total from 3 different counts ?

Mar 19, 2015 05:36 AM|purplepint|LINK

Here is something similar I did recently to subtract one SQL query from another. I guess you can use the same principal to add three queries.

```USE [db123]
GO

/****** Object:  StoredProcedure [dbo].[CountAvailableBookings]    Script Date: 03/19/2015 09:33:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CountAvailableBookings] @Day int, @Date date, @ContractorID int, @NoOfBookings int OUTPUT
AS
BEGIN

DECLARE @DayBookings int
DECLARE @NoActualBookings int

IF @ContractorID = 0
BEGIN
SELECT @DayBookings = COUNT(*) FROM BookingSettings WHERE ([Days] = @Day)
SELECT @NoActualBookings = COUNT (*) FROM Bookings INNER JOIN Jobs ON Bookings.JobID = Jobs.JobID WHERE BookingDate = @Date
END
ELSE
BEGIN
SELECT @DayBookings = COUNT(*) FROM BookingSettings WHERE [Days] = @Day AND ContractorID = @ContractorID
SELECT @NoActualBookings = COUNT (*) FROM Bookings INNER JOIN Jobs ON Bookings.JobID = Jobs.JobID WHERE Bookings.BookingDate = @Date AND Jobs.ContractorID = @ContractorID
END

SET @NoOfBookings = @DayBookings - @NoActualBookings

END

GO

```

• gimimex

Contributor

5460 Points

1123 Posts

### Re: How to get a total from 3 different counts ?

Mar 19, 2015 07:17 AM|gimimex|LINK

Hi,

Try adding in the final part of the query I suggested:

`    coalesce(o.Total, 0) + coalesce(t.Total, 0) + coalesce(c.Total, 0) as Total`

Hope this helps.

• ricas

Member

41 Points

409 Posts

### Re: How to get a total from 3 different counts ?

Mar 19, 2015 09:25 AM|ricas|LINK

Thanks !

#### gimimex

Hi,

Try adding in the final part of the query I suggested:

`    coalesce(o.Total, 0) + coalesce(t.Total, 0) + coalesce(c.Total, 0) as Total`

Hope this helps.

The query looks like this now;

WITH CTE_C AS (SELECT DISTINCT inst, opc, dato
FROM            opc
WHERE        (dato > '01 - 01 - 2015') AND (dato > DATEADD(DAY, - 365, GETDATE()))), CTE_Opc AS
(SELECT        inst, SUM(CASE WHEN opc IN ('A', 'C', 'E') THEN 2 ELSE 1 END) AS Total
FROM            CTE_C AS CTE_C_1
GROUP BY inst), CTE_tting AS
(SELECT        COUNT(DISTINCT DATEDIFF(dd, 0, dato)) AS Total, inst
FROM            tting
WHERE        (dato > DATEADD(DAY, - 365, GETDATE()))
GROUP BY inst), CTE_cgrade AS
(SELECT        COUNT(DISTINCT DATEDIFF(dd, 0, dato)) AS Total, inst
FROM            cgrade
WHERE        (dato > DATEADD(DAY, - 365, GETDATE()))
GROUP BY inst)
SELECT        COALESCE (o.Total, 0) + COALESCE (t.Total, 0) + COALESCE (c.Total, 0) AS Total, o.inst
FROM            CTE_Opc AS o FULL OUTER JOIN
CTE_tting AS t ON t.inst = o.inst FULL OUTER JOIN
CTE_cgrade AS c ON c.inst = o.inst OR c.inst = t.inst

The count is correct, but some of the inst names are left out in this query - and not in the previous you suggested ?

Best regards

_________________________________________________

..... I am new to all this, so please be patient
• gimimex

Contributor

5460 Points

1123 Posts

### Re: How to get a total from 3 different counts ?

Mar 19, 2015 11:01 AM|gimimex|LINK

Try to keep the final part as follows:

```select
coalesce(o.inst, t.inst, c.inst) as inst,
coalesce(o.Total, 0) + coalesce(t.Total, 0) + coalesce(c.Total, 0) as Total
from CTE_Opc as o
full join CTE_tting as t
on t.inst = o.inst
full join CTE_cgrade as c
on c.inst = o.inst or
c.inst = t.inst```

Hope this helps.

• ricas

Member

41 Points

409 Posts

### Re: How to get a total from 3 different counts ?

Mar 19, 2015 11:48 AM|ricas|LINK

#### gimimex

Try to keep the final part as follows:

```select
coalesce(o.inst, t.inst, c.inst) as inst,
coalesce(o.Total, 0) + coalesce(t.Total, 0) + coalesce(c.Total, 0) as Total
from CTE_Opc as o
full join CTE_tting as t
on t.inst = o.inst
full join CTE_cgrade as c
on c.inst = o.inst or
c.inst = t.inst```

Hope this helps.

It sure did, thanks a lot !

Best regards

_________________________________________________

..... I am new to all this, so please be patient