CODEWITHSUNDEEP
joincount
Gregory Brauninger
Gregory Brauninger

Reputation: 75

Joining Two Count Queries

So this is where I'm at (and this works exactly as needed). However this is just step one of the project. So the goal is to have this result of this query to display on sheet one via ODBC to excel (never done this so going to have to do a lot of research). Once displayed in excel user will be able to input date range into...let's say A1 or B1 (not sure yet on design). So this is really good what I have so far. So now I am looking to make second sheet with more information then just failed/success. This second sheet will do exactly what the first sheet does just display more details. THIS WILL ONLY APPLY FOR FAILS.

SELECT Sum(case when status = 6 then 1 else 0 end) as Failed,
       Sum(case when status = 9 then 1 else 0 end) as Successful,
   UniqueID
Into #tempsheet1
FROM Documents
WHERE ownerID = 467
and status in (6,9)
and CreationTime between @StartDate and @EndDate
Group By UniqueID

Select D.UniqueID, FromName, ToName, CreationTime,
cast(CreationTime as date) as CreationDate, cast(CreationTime as date) as CreationTime,
ErrorCode, ElapsedSendTime, RemoteID
From #tempsheet1 ts1
Inner Join Documents D On
D.UniqueID = ts1.UniqueID
and [Status] = 9

Upvotes: 0

Views: 49

Answers (3)

xQbert
xQbert

Reputation: 35323

You could use conditional aggregation and sum vs count...

SELECT Sum(case when status = 6 then 1 else 0 end) as Failed,
       Sum(case when status = 9 then 1 else 0 end) as Successful,
       cast(CreationTime as date) CreationDate
FROM Documents
WHERE ownerID = 467
  and status in (6,9)
  and CreationDate between @StartDate and @EndDate
GROUP BY cast(CreationTime as date) CreationDate
ORDER BY cast(CreationTime as date) descending

Since both queries use documents table and the same ownerID filter, we can just filter for owerID in the where clause. I add status of 6,9 in the where caluse since we could have lots of other status we don't care about and why bother evaluating them?

The sum(case evaluates status and when 6 sets a counter to 1 when failed otherwise it sets the counter to 0) This way the sum of all the 1's and 0's will result in the "count" of failed or successful.

Upvotes: 1

LONG
LONG

Reputation: 4610

SELECT A.Failed,B.Successful  --before it is '*' which means 'all columns'
FROM 
(
SELECT COUNT([Status]) as Failed, 1 as JoiningValue
    FROM Documents
    Where ownerid = '467'
    and [Status] = '6') as A

INNER JOIN

(
SELECT COUNT([Status]) as Successful, 1 as JoiningValue
    FROM Documents
    Where ownerid = '467'
    and [Status] = '9') as B

ON B.JoiningValue = A.JoiningValue

Upvotes: 1

K.Suthagar
K.Suthagar

Reputation: 2306

Just you can SELECT this two queries, then you can get that values in one table.

SELECT 
(SELECT COUNT([Status]) as Failed
into #tempfailed
FROM Documents
Where ownerid = '467'
and [Status] = '6') sa FAILED,
(SELECT COUNT([Status]) as Successful
FROM Documents
Where ownerid = '467'
and [Status] = '9') as Sucess

Upvotes: 0

Related Questions