Sql-server – T-SQL query – how to AVG() the SUM() of columns’ rows to a new row column

querysql serversubqueryt-sql

What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.

How should the query look like to achieve this, since the AVG function is an aggregate one that is retrieving the vertical average column-wise but not the horizontal. Is there an elegant way to achieve this by a query?

Could you please elaborate the below query on the screenshot or modify it so that it works? Is there an elegant way to make this using AVG and COUNT() only without the trivial sum/count?

I have the following solution on the screenshot to extend more so that it does the job but for me it seems not to be correct:

Please find the below code (my trivial solution as a sum and division by the number of the summed fields):


USE [Students]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
 [StudentID] [int] NOT NULL,
 [StudentName] [nchar](100) NOT NULL,
 [StudentSurname] [nchar](100) NOT NULL,
 [Biology] [decimal](3, 2) NOT NULL,
 [Maths] [decimal](3, 2) NOT NULL,
 [Geography] [decimal](3, 2) NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
 [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (1, N'Mike                                                                                                ', N'Manson                                                                                              ', CAST(2.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(1.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (2, N'Timo                                                                                                ', N'Torn                                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (3, N'Jeffrey                                                                                             ', N'Jones                                                                                               ', CAST(1.00 AS Decimal(3, 2)), CAST(3.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO

SELECT * FROM Students;

SELECT
(SELECT Biology + Maths + Geography)/3 AS Mark_average
FROM Students;

Here is the output of the above query:

Mark_average
1.666666
2.333333
2.666666

Is there an alternative and more elegant/sophisticated way (similar to what is on the screenshot, by e.g. a subquery using AVG() of SUM() function) to retrieve the result?

Thanks much in advance!

Best Answer

If you are looking for a different solution, you could always unpivot the data, aggregate and then join back on the source table. Here is an example.

Also, thank you so much for including sample table and data to work with, so much easier.

;WITH CTE_Unpivot AS
    (
    SELECT StudentID, MarkSubject, Mark 
    FROM dbo.Students AS S
        UNPIVOT (Mark FOR MarkSubject IN (Biology, Maths, [Geography])) AS upvt
    )
, CTE_Average AS
    (
    SELECT StudentID, AVG(Mark) AS StudentAverage
    FROM CTE_Unpivot AS U
    GROUP BY StudentID 
    )
SELECT S.StudentID
    , S.StudentName
    , S.StudentSurname
    , S.Biology
    , S.Maths
    , S.[Geography]
    , A.StudentAverage
FROM dbo.Students AS S
    LEFT OUTER JOIN CTE_Average AS A ON S.StudentID = A.StudentID

I feel the need to point out that this is less efficient than just adding the fields together and dividing them. Manipulating the data like this touches it multiple times, which means that it is more likley to spill to tempdb and have a higher cpu cost. Probably negligible, but still.

Related Solutions

Sql-server – Combine column from multiple rows into single row

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

Mysql – How to select/count rows where the same value exists in a column but not in other column all at the same table

SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON  A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;

I loaded you sample data (I added an additional index)

mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
    ->   `id_user_rq` int(11) NOT NULL,
    ->   `id_user_ap` int(11) NOT NULL,
    ->   UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
    ->   UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
    -> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          1 |
|          2 |          2 |
|          2 |          3 |
+------------+------------+
6 rows in set (0.00 sec)

mysql>

Here is the result of my answer

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
|          2 |          3 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL
    -> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
+------------+------------+
2 rows in set (0.01 sec)

mysql>

Best Answer

Related Solutions

Sql-server – Combine column from multiple rows into single row

Mysql – How to select/count rows where the same value exists in a column but not in other column all at the same table

Related Question