1

I am trying to add a Total line at the bottom of this sql syntax but thus far have received no break through. I have viewed the following but both of them do not adhere with my condition. Can someone please provide assistance with regards to this.

Add a summary row with totals

Adding a total row to the end of query result

select dm.Builder ||' ('|| dm.Lot_Size || '''s)' as"Builder",count(sd.Address) "The Count",
dm."Construction_ID"
from input dm
left join data sd on sd.inputfk = dm.inputpk
and sd.Closing Date >= DATE '01/01/2017' and sd.Closing Date < DATE '06/30/2017'
where dm.Construction_ID = 'AJR'
group by dm.Builder,dm.Lot_Size, dm.Project_ID
having count(sd.Address) > 0
order by dm.Builder

When I run it:

  Builder            The Count     Construction_ID 
Jake's Homes (55's)     2               AJR
Jake's Homes (65's)     3               AJR
Maggie's Homes (65's)   5               AJR
Maggie's Homes (66's)   2               AJR
Maggie's Homes (75's)   3               AJR
Maggie's Homes (90's)   1               AJR

 Total ---------->     16
Community
  • 1
  • 1
Jake Wagner
  • 786
  • 2
  • 12
  • 29
  • 1
    `I am oblivious on why the result shows ones redundantly for the same Builders and Lot Size combinations even though I have grouped by ALL the columns in the script.` -- may be because of `sd.Address` ? – Oto Shavadze Feb 14 '17 at 17:18
  • Yeah that works needed to remove `sd.Address`. – Jake Wagner Feb 14 '17 at 17:21

3 Answers3

2

Your group by has dm.Project_ID, sd.Address which is probably causing it.

For total, you can use ROLLUP:

Try this:

select coalesce(dm.Builder || ' (' || dm.Lot_Size || '''s)', 'Total') as "Builder",
    count(sd.Address) "The Count",
    dm."Construction_ID"
from input dm
left join data sd on sd.inputfk = dm.inputpk
    and sd.Closing date >= date '01/01/2017'
    and sd.Closing date < date '06/30/2017'
where dm.Construction_ID = 'AJR'
group by rollup(dm.Builder || ' (' || dm.Lot_Size || '''s)')
having count(sd.Address) > 0
order by "Builder"
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
1

Try this:

select dm.Builder ||' ('|| dm.Lot_Size || '''s)' as"Builder",count(sd.Address) "The Count",
dm."Construction_ID"
from input dm
left join data sd on sd.inputfk = dm.inputpk
and sd.Closing Date >= DATE '01/01/2017' and sd.Closing Date < DATE '06/30/2017'
where dm.Construction_ID = 'AJR'
group by rollup( (dm.Builder,dm.Lot_Size, dm.Project_ID) )
having count(sd.Address) > 0
order by dm.Builder

Just... why you need count(sd.Address) > 0 ?

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
0

Given the post is tagged with postgresql, assuming it is for that platform; as such, see https://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS

user1327961
  • 452
  • 2
  • 8