Solved

VB.net SQL show all of one field in aggregate function

Posted on 2014-02-19
2
196 Views
Last Modified: 2014-02-19
Hi
I am using the following SQL statement to get the total Amount for each company.
The result only shows a few companies. I need ll companies to be returned even if the aggregate is null or zero. How do I achieve this? Should I do an outer join on a select that selects just company or is there an easier methos?

SELECT [Company],Sum(Amount) As Aggregate FROM Table_Pivot)  Group By [Company]
0
Comment
Question by:murbro
2 Comments
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 39869914
you need to start from a table containing all the companies.

select *
from Companies as C
left join (
   SELECT [Company],Sum(Amount) As Aggregate
   FROM Table_Pivot  
   Group By [Company]
) as T
on C.CompanyField = T.Company
0
 

Author Closing Comment

by:murbro
ID: 39869915
Thanks very much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now