To Calculate Sum() Two Alias Named Columns - in SQL

To calculate sum() two alias named columns - in sql

You can't do it directly - you need to use something like a CTE (Common Table Expression) - like this:

;WITH sums AS 
(
   SELECT
      m1, m2, 
      SUM(m1) + SUM(m2) as Total,
      SUM(m1) + SUM(m2) as Total1 
   FROM
      dbo.stud 
   GROUP BY
      m1, m2
)
SELECT 
   m1, m2, 
   total, total1, 
   total+total1 AS 'GrandTotal' 
FROM 
   sums

This works in SQL Server 2005 and newer (and also in some other database systems that support CTE's - which is an ANSI standard).

How to use the sum of two aliases in an order by clause in postgres?

Do it like this:

select * from (
SELECT *, 
    (SELECT COUNT(*) FROM upvote up WHERE up.post_id = post.id) AS upvotes,
    (SELECT COUNT(*) FROM downvote dw WHERE dw.post_id = post.id) AS downvotes
    FROM post) A
ORDER BY (upvotes+downvotes) DESC;

Here is a small DEMO for Postgresql 12.

How to calculate sum from Alias column in MySQL?

Yes, you can't use the alias at the same level. You need to write an upper layer of query to use the alias
Considering the logic of the code is already working, just correcting the syntax to use alias
Here is how it is

SELECT 
   sum(CASE when quality_class =1 then trees_ha else 0 end)/get_total_forest_plots() + sum(CASE when quality_class =3 then trees_ha else 0 end)/get_total_forest_plots())/sum(initial_data.total) *1
FROM
    (SELECT 
    sum(CASE when quality_class =1 then trees_ha else 0 end)/get_total_forest_plots() + sum(CASE when quality_class =2 then trees_ha else 0 end)/get_total_forest_plots() as total 
From
<Table name> 
) initial_data

Is there a way to calculate a SUM of a Count Alias in SQL?

You could use analytic functions here:

SELECT
    WrapupData,
    ISNULL(WrapupData, 'No Dispos Code Entered') AS DispositionCode,
    COUNT(WrapupData) AS DispositionCount,
    SUM(COUNT(WrapupData)) OVER () AS Total,
    100.0 * COUNT(WrapupData) / SUM(COUNT(WrapupDatalse)) OVER () AS Percentage
FROM Termination_Call_Detail tcd 
LEFT JOIN dbo.t_Call_Type ct
    ON ct.CallTypeID = tcd.CallTypeID
GROUP BY
    WrapupData;

The here is to use SUM() with a window over the entire table, post aggregation, to find the total. We can also find the percentage by normalizing the count using this sum.

---

Related Topics