How to use count in subquery in MSSQL

Question

I would like to merge two tables in mssql. The first Table have a task column. I would like to count the specific tasks and give the counted result to the second table to the AuftNr.

Here

Do i need a subquery and group by to solve this ?

So far i have done this.

    SELECT AB.PersNr as PersonalNumber

      ,CONVERT(char(10),DATEADD(DAY, AB.Tag, '30.12.1899'),126) AS Day
      ,CONVERT(char(10),DATEADD(SECOND, AB.Von, DATEADD(DAY, AB.Tag, 
   '30.12.1899')),108) AS [From]
      ,AB.Bis as [To]
      ,AB.Auftrag as Task
   FROM AStpVonBis AB
    LEFT JOIN Auftrag A ON (A.AuftNr = AB.Auftrag)
    INNER JOIN Personen P ON (P.PersNr = AB.PersNr)
   WHERE P.Abteilung = 170 AND AB.Tag = DATEDIFF(DAY, '30.12.1899', GETDATE()) 
   AND AB.Bis = -2 


   SELECT  A.AuftNr FROM Auftrag A

Please share the sample data from both the tables and your expected result with all the conditions. — Ankit Bajpai
– Ankit Bajpai, Commented Jun 28, 2017 at 11:19

LukStorms · Accepted Answer · 2017-06-30 10:02:31Z

1

Using a GROUP BY and a COUNT should do it :

SELECT 
 AB.Auftrag as Task,
 count(*) as Total
FROM AStpVonBis AB
JOIN Personen P ON (P.PersNr = AB.PersNr)
WHERE P.Abteilung = 170 
AND AB.Tag = DATEDIFF(DAY, convert(date,'30.12.1899',104), GETDATE())
AND AB.Bis = -2
GROUP BY AB.Auftrag
ORDER BY AB.Auftrag

Note that the left join with [Auftrag] wasn't included.
Since there's already AB.Auftrag to group by, and there's no grouping needed on the name of the Task.

The date stamp is converted with the 104 format to a date.
Just so it'll also work on connections that use another default date format.

Disclaimer: only tested in notepad

edited Jun 30, 2017 at 10:02

answered Jun 28, 2017 at 15:11

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LukStorms Over a year ago

@GregOstry That's good to hear. I wasn't sure if you also needed to get the Tasks with count 0. In which case it would need an additional RIGHT JOIN to [Auftrag] and other changes. But I guess you didn't need that.

JonWay · Accepted Answer · 2017-06-28 14:50:30Z

If I understand your question correctly, the bellow query should work

DECLARE @Count TABLE (PhoneNumver INT, [Day] DATE,[From] VARCHAR(150),[To] VARCHAR(3),Task INT)
INSERT INTO @Count  
VALUES(1003,'2017-06-28','07:46:20','-2',150 ),
      (1010,'2017-06-28','11:44:47','-2',140),
      (1012,'2017-06-28','10:57:00','-2',120 ),
      (1016,'2017-06-28','12:20:16','-2',120 ), 
      (1019,'2017-06-28','08:31:03','-2',120 ),
      (1020,'2017-06-28','11:38:02','-2',120 ),
      (1021,'2017-06-28','07:54:55','-2',120 ),
      (1025,'2017-06-28','11:38:12','-2',120 ),
      (1027,'2017-06-28','09:47:46','-2',130 )

DECLARE @Task TABLE (AuftNr INT)
INSERT INTO @Task VALUES (110),(120),(130),(140),(150),(200),(210),(220),(230)


SELECT
A.AuftNr,
COUNT(C.Task) AS Total_Count
FROM @Task A
LEFT JOIN @Count C ON A.AuftNr=C.Task

--From here you can add all the exclussions in where clause 
GROUP BY A.AuftNr
ORDER BY Total_Count DESC

OUTPUT

AuftNr  Total_Count
120         6
130         1
140         1
150         1
200         0
210         0
220         0
230         0
110         0

Collectives™ on Stack Overflow

How to use count in subquery in MSSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related