Multiple queries -> columns. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple queries -> columns.

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-10-20 : 10:58:38

I have a table of AwardTypes and Amounts. There are 3 different Award types; A, B, and C. I want to make a Select statement that produces a table of Sum(Amounts) by Type. I want the columns to be A, B, and C, and a single row of the Sums. I've been able to produce a table with a row for each Type, but not a Column for each type. How would I do that?
So, roughly, what I want is:
Select Sum(Amount) where Type=A, Sum(amount) where Type=B, Sum(amount) where Type=C from Awards.
But SQL doesn't like the comma following the A.
Thanks.

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-10-20 : 11:30:13

I sort of figured it out with:
Select Sum(Amount) as Total, Type from Awards Group by Type.

Is there an easy way to make sure I have a row for each Type, even if there are no Awards of that type? Specifically, to show that Type listed with a 0 in the Total column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:50:38

quote:
Originally posted by DaveBF

I sort of figured it out with:
Select Sum(Amount) as Total, Type from Awards Group by Type.

Is there an easy way to make sure I have a row for each Type, even if there are no Awards of that type? Specifically, to show that Type listed with a 0 in the Total column?

For that you need to use master table which contain all type details. Can you post the table details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-20 : 13:34:40

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Is this what you meant?

CREATE TABLE AwardTypes
(award_type CHAR(1) NOT NULL PRIMARY KEY,
award-description VARCHAR(50) NOT NULL);

This is a pretty useless table and coudl be repqalced witha CHECK() constraint.

>> .. and Amounts. <<

Am amoung is an attribute, not an entity.

CREATE TABLE Awards
(award_type CHAR(1) NOT NULL
REFERENCES AwardTypes(award_type),
award_recipient CHAR(10) NOT NULL,
award_amt DECIMAL (8,2) NOT NULL,
PRIMARY KEY (??));

>>SELECT statement that produces a table of SUM(award_amt) by award_type. <<

SELECT award_type, SUM(award_amt) AS award_amt_tot
FROM Awards
GROUP BY award_type;

>> I want the columns to be A, B, and C, and a single row of the SUMs. <<

Good SQL programmers would do the display formatting in the front end, not in the data base, but here is the code:

SELECT SUM(CASE WHEN award_type = 'A' THEN award_amt ELSE 0.00 END) AS award_A_tot,
SUM(CASE WHEN award_type = 'B' THEN award_amt ELSE 0.00 END) AS award_B_tot,
SUM(CASE WHEN award_type = 'C' THEN award_amt ELSE 0.00 END) AS award_C_tot
FROM Awards
GROUP BY award_type;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources