CODEWITHSUNDEEP
sqlsql-server
Adam Caudell
Adam Caudell

Reputation: 43

Condensing the results of a SQL Query

I have a query that pulls student attendance data over a specified date range. I would like to somehow "flatten" the data to give me a single line per student with a count of the attendance marks per class period. My current results look something like this 

Student Name    PermID      Date        Per1    Per2    Per3    Per4
Student1        11786       2018-12-04   U       U       U       U  
Student2        603697      2018-12-07           U       U                  
Student2        603697      2018-12-10           U       M                  
Student2        603697      2018-12-11           U               L                  
Student2        603697      2018-12-14   U       U       L
Student3        1733        2018-11-27                           L
Student3        1733        2018-12-03                           L

but I would likst the results to look like this:

Student Name    PermID      Per1    Per2    Per3    Per4
Student1        11786        1       1       1       1  
Student2        603697       1       4       3       1          
Student3        1733         0       0       0       2

I don't care about the date in my result since my SQL code only looks back 30 days. Here is a sample of my query:

SELECT 
  CONCAT(STU.LN, ', ', STU.FN) AS [Student Name], 
  STU.ID AS [PermID], 
  CONVERT(date,ATT.DT) AS [Date], 
  ATT.A1 AS Per1, 
  ATT.A2 AS Per2, 
  ATT.A3 AS Per3, 
  ATT.A4 AS Per4, 

FROM STU
   LEFT JOIN ATT 
    ON (STU.SN = ATT.SN AND STU.DEL = 0)

WHERE 
  (
  ATT.A1 in ('L', 'M', 'A', 'T', 'U') OR 
  ATT.A2 in ('L', 'M', 'A', 'T', 'U') OR 
  ATT.A3 in ('L', 'M', 'A', 'T', 'U') OR 
  ATT.A4 in ('L', 'M', 'A', 'T', 'U') OR 
  ) AND
  ATT.DT >= GETDATE()-30 AND
  ATT.DT != '2018-10-10' AND
  STU.SP <> 'E'

ORDER BY STU.LN, STU.FN, STU.ID, ATT.DY

Can this query be rewritten to aggregate the count of attendance codes per period by student so that my data set is flattened to only 1 row per student?

Any and all help is appreciated.

Upvotes: 0

Views: 373

Answers (3)

EzLo
EzLo

Reputation: 14199

You can use GROUP BY with studentname and permID, then a COUNT for each per.

;WITH YourData AS
(
    SELECT 
      CONCAT(STU.LN, ', ', STU.FN) AS [Student Name], 
      STU.ID AS [PermID], 
      CONVERT(date,ATT.DT) AS [Date], 
      ATT.A1 AS Per1, 
      ATT.A2 AS Per2, 
      ATT.A3 AS Per3, 
      ATT.A4 AS Per4, 
    FROM STU
       LEFT JOIN ATT 
        ON (STU.SN = ATT.SN AND STU.DEL = 0)
    WHERE 
      (
          ATT.A1 in ('L', 'M', 'A', 'T', 'U') OR 
          ATT.A2 in ('L', 'M', 'A', 'T', 'U') OR 
          ATT.A3 in ('L', 'M', 'A', 'T', 'U') OR 
          ATT.A4 in ('L', 'M', 'A', 'T', 'U') OR 
      ) AND
      ATT.DT >= GETDATE()-30 AND
      ATT.DT != '2018-10-10' AND
      STU.SP <> 'E'
)
SELECT
    T.[Student Name],
    T.PermID,
    Per1 = COUNT(T.Per1),
    Per2 = COUNT(T.Per2),
    Per3 = COUNT(T.Per3),
    Per4 = COUNT(T.Per4)
FROM
    YourData AS T
GROUP BY
    T.[Student Name],
    T.PermID

When you want to "flatten" you should think of GROUP BY. All columns that are not grouped by will be lost (the SQL engine can't pick one to show, as you are "flattening" N rows by group) unless they are used in an aggregation operation, like COUNT, SUM, AVG, FIRST_VALUE, etc.

You can also use expressions on those values before the "flatenning" occurs, like for example:

Per3WithUValuesOnly = COUNT(CASE WHEN Per3 = 'U' THEN 1 END)

Upvotes: 2

Eric Brandt
Eric Brandt

Reputation: 8101

As a counter point to Gordon, assuming the "blank" values are empty strings, conditional aggregation should work.

SELECT 
  CONCAT(STU.LN, ', ', STU.FN) AS [Student Name], 
  STU.ID AS [PermID], 
  CONVERT(date,ATT.DT) AS [Date], 
  SUM(CASE WHEN ATT.A1 <> '' THEN 1 ELSE 0 END) AS Per1, 
  SUM(CASE WHEN ATT.A2 <> '' THEN 1 ELSE 0 END) AS Per2, 
  SUM(CASE WHEN ATT.A3 <> '' THEN 1 ELSE 0 END) AS Per3, 
  SUM(CASE WHEN ATT.A4 <> '' THEN 1 ELSE 0 END) AS Per4, 

FROM STU
   LEFT JOIN ATT 
    ON (STU.SN = ATT.SN AND STU.DEL = 0)

WHERE 
  (
  ATT.A1 in ('L', 'M', 'A', 'T', 'U') OR 
  ATT.A2 in ('L', 'M', 'A', 'T', 'U') OR 
  ATT.A3 in ('L', 'M', 'A', 'T', 'U') OR 
  ATT.A4 in ('L', 'M', 'A', 'T', 'U') OR 
  ) AND
  ATT.DT >= GETDATE()-30 AND
  ATT.DT != '2018-10-10' AND
  STU.SP <> 'E'
GROUP BY 
  CONCAT(STU.LN, ', ', STU.FN), 
  STU.ID, 
  CONVERT(date,ATT.DT)

ORDER BY STU.LN, STU.FN, STU.ID, ATT.DY

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

I think this just requires aggregation. Assuming that the "blank" values are NULLs:

with t as (
      <your query here with no order by>
     )
select student_name, permid,
       count(per1) as per1,
       count(per2) as per2,
       count(per3) as per3,
       count(per4) as per4
from t
group by student_name, permid

Upvotes: 2

Related Questions