pivot table: Null value eliminated from aggregate

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

SQL Server 2000 Forums

Transact-SQL (2000)

pivot table: Null value eliminated from aggregate

Author

Topic

ly
Starting Member

6 Posts

Posted - 2003-09-21 : 08:36:58

Hi,thanks first for some good answers I got for my last query. My question here is how to keep the NULL value from being eliminated from aggregation.

Query:

select Country, Port,
sum(case Type when 20 then Unit end) as '20',
sum(case Type when 40 then Unit end) as '40',
sum(case Type when '40HC'then Unit end) as '40HC'
from budget
group by Country, Port
select * from t1
order by Country, Port
.........
Result:

Warning: Null value eliminated from aggregate.

(8 row(s) affected)

country port t20 t40 t40hc
------- ---- ----------- ----------- -----------
A A 4 55 5
A B 4 NULL NULL
B A 2 NULL NULL
B B 1 NULL NULL
B C 22 NULL NULL
B D 11 NULL NULL
C A 6 NULL NULL
C B 10 NULL NULL

(8 row(s) affected)

In this example, even if country A, Port C has no count for all types, I want three NULLs to be listed in the result instead of being removed.

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-21 : 09:40:37

Try

GROUP BY IsNull(Country, ''), IsNull(Port, '')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-21 : 11:39:48

select Country, Port,
sum(case Type when 20 then Unit else 0 end) as '20',
sum(case Type when 40 then Unit else 0 end) as '40',
sum(case Type when '40HC' then Unit else 0 end) as '40HC'
from budget
group by Country, Port
select * from t1
order by Country, Port

that will return 0's instead of nulls when there is no match for that particular column.

- Jeff

ly
Starting Member

6 Posts

Posted - 2003-09-22 : 20:25:40

Hi,
I have first tried:

select Country, Port,
sum(case Type when 20 then Unit else 0 end) as '20',
sum(case Type when 40 then Unit else 0 end) as '40',
sum(case Type when '40HC' then Unit else 0 end) as '40HC'
from budget
group by IsNull(Country, ''), IsNull(Port, '')
....................
Result I got is:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'budget.Country' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'budget.Port' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Then I tried:

select Country, Port,
sum(case Type when 20 then Unit else 0 end) as '20',
sum(case Type when 40 then Unit else 0 end) as '40',
sum(case Type when '40HC' then Unit else 0 end) as '40HC'
from budget
group by Country, Port
select * from t1
order by Country, Port

Result I got is:
country port t20 t40 t40hc
------- ---- ----------- ----------- -----------
A A 4 55 5
A B 4 0 0
B A 2 0 0
B B 1 0 0
B C 22 0 0
B D 11 0 0
C A 6 0 0
C B 10 0 0

(8 row(s) affected)

But this is still not what I want. I want a count to be listed for port A,B,C,D for each country, if the count is 0, then I want it listed as:

country port t20 t40 t40hc
------- ---- ----------- ----------- -----------
A C 0 0 0

I appreciate any answer given!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 20:34:15

please give us some sample data and some table structures. we have no idea what data you have to work with, or how it is structured !

- Jeff

ly
Starting Member

6 Posts

Posted - 2003-09-23 : 05:02:20

Hi Jeff,
The following is the table "budget".

Country Port Type Unit
A A 20 4
A B 20 4
B A 20 2
B B 20 1
B C 20 22
B D 20 11
C A 20 6
C B 20 10
A A 40 55
A A 40HC 5

The following is the final report needed to be generated from the above table.

20¡¯ 40¡¯ 40HC
Country A
Port A 4 55 5
Port B 4
Port C
Port D
Total 8 55 5
Country B
Port A 2
Port B 1
Port C 22
Port D 11
Total 36 0 0
Country C
Port A 6
Port B 10
Port C
Total 16 0 0

I realize that in the orginal table, there is no such row as country A, port C. So this may be the reason my query couldn't generate a result as A C 0 0 0. I wonder if there is another way to do it.

Thanks!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-23 : 07:26:59

CROSS JOIN !

you need to have two tables in your database: a table of all Countries, and a table of all Ports. If you have good relational integrity in your database, you should already have these.

Now, you already know how to write a query that returns data in this form:

Country, Port, 20, 40, 40HC

since you know how to write that query, I won't do it again for you, but we'll call the SQL to do that "SQL1".

Since you need all combinations of Countries and Ports, a cross join does that for you:

run this and check out the results:


SELECT 
  C.Country, P.Port
FROM
  Countries C
CROSS JOIN 
  Ports P

call that "SQL2". Now, you just need to combine the results. the "Driving" query will be SQL2 because that contains all the rows you need. The other query will be SQL1, which contains the actual data you wish to display, but some missing rows. Thus, we do a LEFT OUTER JOIN from SQL2 to SQL1:



SELECT a.POrt, a.Country, b.[20],b.[40],b.[40HC]
FROM 
  (SQL2) a
LEFT OUTER JOIN
  (SQL1) B
ON
  a.Port = B.POrt AND
  a.Country = B.Country

Note we return Port and Country from the query of all ports and all countries, and the "data fields" from the outer query. Surround the 20,40 and 40HC parts with ISNULL(,0) if you wish to return 0's instead of Nulls.

Please let me know if this helps.
- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-23 : 07:48:50