1 Replies - 1305 Views - Last Post: 21 June 2012 - 02:40 PM

#1 varaprasadreddy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 22-October 09

How to display null rows when data exists in MY SQL

Posted 20 June 2012 - 09:22 PM

select p.partnerid,
sum(case when c.amount is not null then c.amount else 0 end) as amount,
sum(case when c.netamt is not null then c.netamt else 0 end) as netamt,
sum(case when c.netamt - c.amount is not null then c.netamt - c.amount else 0 end) as interest,
sum(case when c.installment = 20 then c.amount else 0 end) as Twenty,
sum(case when c.installment = 10 then c.amount else 0 end) as Ten,
sum(case when c.installment = 5 then c.amount else 0 end) as Five,
sum(case when c.installment = 2 then c.amount else 0 end) as Two
from partnerinfo p left outer join customerinfo c on p.partnerid = c.partnerid
where (c.startdate is null OR (c.startdate >= '2011-3-15' and c.startdate <= '2012-12-30'))
and (c.partnerid is null or c.partnerid) and p.manager = 1
group by p.partnerid


Hi i am new to this forum.

with the help of above query i want to display 3 null rows as shown in the images when the date was changed from 2011-3-15 to 2012-3-15.

for a better understanding see below images...

Posted Image

Posted Image

if any thing goes wrong forgive me.

Is This A Good Question/Topic? 0
  • +

Replies To: How to display null rows when data exists in MY SQL

#2 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: How to display null rows when data exists in MY SQL

Posted 21 June 2012 - 02:40 PM

You need to use an outer query in order to generate the set of rows that you want, then use an inner query to generate the data for those rows. Since you want zero values, I have also used the ISNULL() function, which is handy in these situations.

This is probably the query that you need, though the outer query might need its clauses tweaking depedning on your selection criteria:
SELECT
	p.partnerid,
	ISNULL(s.amount, 0),
	ISNULL(s.netamt, 0),
	ISNULL(s.interest, 0),
	ISNULL(s.Twenty, 0),
	ISNULL(s.Ten, 0),
	ISNULL(s.Five, 0),
	ISNULL(s.Two, 0)
FROM partnerinfo AS p
LEFT OUTER JOIN customerinfo AS c ON c.partnerid = p.partnerid
LEFT OUTER JOIN (
	SELECT
		p.partnerid,
		SUM(c.amount) AS amount,
		SUM(c.netamt) AS netamt,
		SUM(c.netamt - c.amount) AS interest,
		SUM(CASE WHEN c.installment = 20 THEN c.amount ELSE 0 END) AS Twenty,
		SUM(CASE WHEN c.installment = 10 THEN c.amount ELSE 0 END) AS Ten,
		SUM(CASE WHEN c.installment = 5 THEN c.amount ELSE 0 END) AS Five,
		SUM(CASE WHEN c.installment = 2 THEN c.amount ELSE 0 END) AS Two
	FROM partnerinfo AS p
	LEFT OUTER JOIN customerinfo AS c ON c.partnerid = p.partnerid
	WHERE (
		c.startdate IS NULL
		OR c.startdate BETWEEN '2011-3-15' AND '2012-12-30'
	) AND (
		c.partnerid IS NULL
		OR c.partnerid
	) AND p.manager = 1
) AS s
WHERE (
	c.partnerid IS NULL
	OR c.partnerid
) AND p.manager = 1


This post has been edited by e_i_pi: 21 June 2012 - 02:41 PM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1