I need to get a count of records, including column Names grouped by UnitName. What am I doing wrong?
-
Hello SQL Gurus, I am seriously struggling with this. I would like the following records to display column names along with total records belonging to each division(e.division). However, I can't even get the records to display the correct results. It displays all the records correctly but does not indicate how many records belong to each division. What am I doing wrong?
SELECT e.empnum, a.filenameName, a.filePath, e.empname, e.department, e.UnitName,x.total
FROM
Employee e INNER JOIN Applyers a ON a.EmpNum = e.empnum
JOIN (SELECT total = COUNT(*) FROM Employee e INNER JOIN Applyers a ON a.EmpNum = e.empnum) AS x ON a.EmpNum = e.empnum
where e.Department='IT'Any ideas what I am doing wrong?
-
Hello SQL Gurus, I am seriously struggling with this. I would like the following records to display column names along with total records belonging to each division(e.division). However, I can't even get the records to display the correct results. It displays all the records correctly but does not indicate how many records belong to each division. What am I doing wrong?
SELECT e.empnum, a.filenameName, a.filePath, e.empname, e.department, e.UnitName,x.total
FROM
Employee e INNER JOIN Applyers a ON a.EmpNum = e.empnum
JOIN (SELECT total = COUNT(*) FROM Employee e INNER JOIN Applyers a ON a.EmpNum = e.empnum) AS x ON a.EmpNum = e.empnum
where e.Department='IT'Any ideas what I am doing wrong?
Your total is not going to make any sense as you are returning every Employee/Applyer record and putting the total on each record. You need to craft your query better, get your granular data, including the departmentno into a query first. Then decide what you want to see from the results. What you probably don'y want is multiple employee records with the department total on each line.
Never underestimate the power of human stupidity RAH
-
Hello SQL Gurus, I am seriously struggling with this. I would like the following records to display column names along with total records belonging to each division(e.division). However, I can't even get the records to display the correct results. It displays all the records correctly but does not indicate how many records belong to each division. What am I doing wrong?
SELECT e.empnum, a.filenameName, a.filePath, e.empname, e.department, e.UnitName,x.total
FROM
Employee e INNER JOIN Applyers a ON a.EmpNum = e.empnum
JOIN (SELECT total = COUNT(*) FROM Employee e INNER JOIN Applyers a ON a.EmpNum = e.empnum) AS x ON a.EmpNum = e.empnum
where e.Department='IT'Any ideas what I am doing wrong?
@samflexx, I have simulated your situation and have made a solution that I think matches your requirement. Since you said that
total records belonging to each division
the obvious idea that cropped up is to use the ROLLUP function. I am presenting the solution. Let us know if it has helped you
;WITH CTE AS(
SELECT
1 AS EMPNUM
,'File1'AS FILENAME
,'Name1' AS EMPNAME
,'Division1' AS Division UNION ALL
SELECT
2 AS EMPNUM
,'File2'AS FILENAME
,'Name2' AS EMPNAME
,'Division2' AS Division UNION ALL
SELECT
3 AS EMPNUM
,'File12'AS FILENAME
,'Name12' AS EMPNAME
,'Division1' AS Division UNION ALL
SELECT
4 AS EMPNUM
,'File14'AS FILENAME
,'Name14' AS EMPNAME
,'Division1' AS Division UNION ALL
SELECT
5 AS EMPNUM
,'File21'AS FILENAME
,'Name21' AS EMPNAME
,'Division2' AS Division UNION ALL
SELECT
6 AS EMPNUM
,'File3'AS FILENAME
,'Name3' AS EMPNAME
,'Division3' AS Division) -- table create and data insertion part-- Query starts
SELECT
X.*
FROM(
SELECT
EMPNUM = ISNULL(CAST(t.EMPNUM AS VARCHAR(10)), ' ')
,FILENAME = ISNULL(CAST(t.FILENAME AS VARCHAR(10)), ' ')
,EMPNAME = ISNULL(CAST(t.EMPNAME AS VARCHAR(10)), ' ')
,Division = CASE
WHEN t.Division IS NULL AND t.EMPNUM IS NULL THEN 'Total :-'
WHEN t.EMPNUM IS NULL THEN 'Total ' + t.Division + ' Count :-'
ELSE t.Division END
,DivisionCount = COUNT(t.Division)
FROM CTE t
GROUP BY ROLLUP(t.Division,t.EMPNUM,t.EMPNAME,t.FILENAME))X
WHERE (LEN(X.FILENAME) > 0 AND LEN(X.EMPNAME) > 0)
OR (LEN(X.FILENAME) = 0 AND LEN(X.EMPNAME) = 0 AND LEN(X.EMPNUM) = 0)The output
EMPNUM FILENAME EMPNAME Division DivisionCount
1 File1 Name1 Division1 1
3 File12 Name12 Division1 1
4 File14 Name14 Division1 1
Total Division1 Count :- 3
2 File2 Name2 Division2 1
5 File21 Name21 Division2 1
Total Division2 Count :- 2
6 File3 Name3 Division3 1
Total Division3 Count :- 1
Total :- 6