SQL union query in columns instead of rows

Question

I wrote a query for recording totals from columns from multiple tables however I could only get the data into rows, how could I get these into columns?

Select Sum(Price) AS [Totals]
From Sold

Union

  SELECT SUM(Total) as Down
    FROM
    (
     SELECT Cost as Total 
     FROM Sold
     UNION ALL
     SELECT Total as Total
     FROM Extras
     ) AS AllRowsFromT1andT2

UNION Select Sum(Valued) AS [Value]
From Sold;

Thanks for any help :)

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 2, 2018 at 4:21
Please always indicate which database you are using. "SQL" - by itelf - just isn't sufficient to identify the syntax needed as answres. (SQL implementation vary a lot between db vendors.) — Paul Maxwell
– Paul Maxwell, Commented Oct 2, 2018 at 4:37

Paul Maxwell · Accepted Answer · 2018-10-02 04:35:47Z

1

I believe you can simplify it like this:

SELECT
    SUM(Price)  AS [Totals]
  , SUM(Valued) AS [Value]
  , SUM(Cost) + (SELECT SUM(Total) FROM Extras) AS [Down]
FROM Sold

you can also prefix the column refrences which although not strictly necessary makes the logic more obvious.

SELECT
    SUM(Sold.Price)  AS [Totals]
  , SUM(Sold.Valued) AS [Value]
  , SUM(Sold.Cost) + (SELECT SUM(Extras.Total) FROM Extras) AS [Down]
FROM Sold

answered Oct 2, 2018 at 4:35

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

Comments

LoztInSpace · Accepted Answer · 2018-10-02 04:27:40Z

0

SELECT
(
Select Sum(Price) AS [Totals]
From Sold
) as Totals,
(
  SELECT SUM(Total) as Down
    FROM
    (
     SELECT Cost as Total 
     FROM Sold
     UNION ALL
     SELECT Total as Total
     FROM Extras
     ) AS AllRowsFromT1andT2
) as AllRowsFromT1andT2,
(
Select Sum(Valued) AS [Value]
From Sold
) as VALUE

SQL Server will work but depending on your database you may need to coerce the syntax. For example in Oracle you would need to SELECT <..all that..> FROM DUAL Not sure about MySQL or others.

answered Oct 2, 2018 at 4:27

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL union query in columns instead of rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related