Summing function in SQL adding zeros and NULLS how to avoid

Question

I am trying to get only three columns back in my end result which would look like outcome A below, but I am getting outcome B instead. I know it has to do with my NULLS and 0 but not sure how to exclude them? If I use a where clause I would need to have a WHERE clause in all three of my flags which not sure how to go about that

Outcome A

R   DIA             SUD_FLAG_PER    ED_3_FLAG_PER
1   11.11111111     5.555555556      2.777777778

OUTCOME B - THIS IS WHAT I AM CURRENTLY GETTING

R   DIA             SUD_FLAG_PER    ED_3_FLAG_PER
1   11.11111111         0   
2   0               5.555555556 
3   0                    0  
4   0                    0          2.777777778

CODE

select


SUM(Diabetes_FLAG)*100/( SELECT percentt
                               from members) as DIA

,SUM(SUD_FLAG)*100/( SELECT percentt
                               from members) as SUD_FLAG_per 

,SUM(ED_3_FLAG)*100/(SELECT percentt
                               from members) as ED_3_FLAG_per                             


From prefinal

Group By Diabetes_FLAG ,SUD_FLAG ,ED_3_FLAG

here is the result from Prefinal Table..SORRY ABOUT THE FORMAT

MED_ID  ED_3_FLAG   SUD_FLAG    DIABETES_FLAG
99017471E       1    0              0
97483445D       0   0
93816600D   1   0   0
97696242G       0   0
95277731G       0   0
95235519A       0   0
90977691G       0   1
93793821A       0   0
96133532A       0   0
94378176C       0   0
94180014F       0   0
93391445F       0   0
98706680C       0   0
96478120E       0   0
92247933C       0   0
98591445F       0   0
98583717D       0   0
97258639C       0   0
90870338A       0   0
93695941A       0   0
91464685C       1   0
95994257F       0   0
94373524E       0   0
91373284C       0   0
97499504C       0   0
91677431D       0   0
99886113D       0   1
92964373F       0   0
90206268E       0   0
93208186A       0   0
92374509A       0   1
95879269E       0   0
92866204A       0   1
93741183C       0   0
90507292A       0   0
92867013C       0   0

members table results 

    PERCENTT
1   36

can you add current table result of table 'prefinal' and 'here' — Mr. Bhosale
– Mr. Bhosale, Commented Jun 4, 2018 at 3:50
I did remove the Group clause and I get an error " Not a single group-group function" — cardonas
– cardonas, Commented Jun 4, 2018 at 4:06
I did add the results from the two tables its pulling from as well — cardonas
– cardonas, Commented Jun 4, 2018 at 4:06
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ... — user330315
– user330315, Commented Jun 4, 2018 at 5:42

Barbaros Özhan · Accepted Answer · 2018-06-04 04:35:26Z

3

Try to JOIN the tables :

SELECT SUM(Diabetes_FLAG * 100/percentt) as DIA,
       SUM(SUD_FLAG * 100 / percentt) as SUD_FLAG_per,
       SUM(ED_3_FLAG * 100 / percentt) as ED_3_FLAG_per
  FROM prefinal CROSS JOIN members;

SQL Fiddle Demo

answered Jun 4, 2018 at 4:35

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cardonas Over a year ago

Thank you so much!!! It worked perfectly ! I am going to have to read up on cross joins to see the logic behind it .. THANK YOU AGAIN !!

Collectives™ on Stack Overflow

Summing function in SQL adding zeros and NULLS how to avoid

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related