Sql use having with where

Question

0.00/5 (No votes)

See more:

SQL

SELECT  reg.[regcor_id]
        ,c1.[cor_start]
        ,c1.[cor_end]
        ,c1.[cor_hours]
        ,reg.[total_sc]
        ,c1.[currency]
        ,cu.[currency]
        ,c1.[rate]
        , c1.[status]
        ,c1.[sem_id]
        ,s1.[semester]
        ,s1.[year]
        ,c1.[lev_id]
        ,l1.[level]
        ,reg.[stu_id]
        ,reg.[stu_reg_id]
        ,ISNULL(SUM(p1.[amount_sc]),0) AS 'paid sc'
        ,reg.[total_fc]
        ,ISNULL(SUM(p1.[amount_fc]),0) AS 'paid fc'
FROM       [DB_SchoolManager].[dbo].[tbl_stu_reg]    AS reg  
INNER JOIN [DB_SchoolManager].[dbo].[tbl_courses]    AS c1 on reg.regcor_id = c1.cor_id 
INNER JOIN [DB_SchoolManager].[dbo].[tbl_currencies] AS cu on c1.[currency] = cu.[currency_id] 
INNER JOIN [DB_SchoolManager].[dbo].[tbl_semester]   AS s1 on c1.sem_id     = s1.sem_id  
INNER JOIN [DB_SchoolManager].[dbo].[tbl_levels]     AS l1 on s1.lev_id     = l1.lev_id 	
LEFT JOIN [DB_SchoolManager].[dbo].[tbl_incoming_payments] AS p1 on reg.stu_reg_id = p1.[stu_reg_id]  
WHERE     reg.stu_id = '1'
GROUP BY  reg.[regcor_id]
          ,c1.[cor_start]
          ,c1.[cor_end]
          ,c1.[cor_hours]
          ,reg.[total_sc]
          ,c1.[currency]
          ,cu.[currency]
          ,c1.[rate],c1.[status]
          ,c1.[sem_id]
          ,s1.[semester]
          ,s1.[year]
          ,c1.[lev_id]
          ,l1.[level]
          ,reg.[stu_id]
          ,reg.[stu_reg_id]
          ,reg.[total_fc]  
HAVING    ISNULL(SUM(reg.[total_fc]),0)> ISNULL(SUM(p1.[amount_fc]),0)

What I have tried:

It still show up the first line which has paidfc=total_fc

regcor_id	cor_start	cor_end	cor_hours	total_sc	currency	currency	rate	status	sem_id	semester	year	lev_id	level	stu_id	stu_reg_id	paid sc	total_fc	paid fc
30	2019-01-01	2019-03-31	355	4974750.0000	2	USD	1507.5000	Opened	1	الفصل الدراسي الأول	2018-2019	1	الأول الثانوي	1	9	4950000.0000	3300.0000	3300.0000
31	2019-01-01	2019-03-31	60	2864250.0000	2	USD	1507.5000	Opened	2	الفصل الدراسي الأول	2018-2019	2	الثاني الثانوي	1	12	0.0000	1900.0000	0.0000
32	2019-04-06	2019-07-27	0	3000000.0000	1	LBP	1.0000	Opened	4	الفصل الدراسي الأول	2018-2019	3	الثالث الثانوي	1	13	0.0000	3000000.0000	0.0000

Posted 21-Jun-19 0:40am

Rabee3-F1.787545

Updated 21-Jun-19 4:31am

#realJSOP

v2

Add a Solution

Comments

#realJSOP 21-Jun-19 7:33am

Your total_fc and paid_fc columns appear to be strings, even though you're doing math with them...

Coding advice - don't put spaces, underscores or other special chars in your table or column names (also avoid using sql reserved words) - it tends to distract a developer. Use camel case instead, such as "TotalFC". Not putting special chars in column names also eliminates the need to use square brackets around their names.

CHill60 21-Jun-19 10:14am

Advice for getting answers in this forum for SQL related questions:
1 - Simplify your query to the bare bones that will demonstrate the problem (this often helps you solve your own problem by the way). Remove output columns that do not help demonstrate the problem, remove joins that do not help demonstrate the problem
2 - Tell us the table structure for all the tables in the simplified query
3 - Provide sample data for all of the tables in the simplified query
4 - show or explain your expected results for the sample data you have provided.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2019-06-21T04:31:00

Solution 1

Quote:
ISNULL(SUM(reg.[total_fc]),0)> ISNULL(SUM(p1.[amount_fc]),0)

You are comparing total_fc and amount_fc. Then you are asking about why a row with paidfc=total_fc. Because your HAVING does not check paidfc.

Posted 21-Jun-19 4:31am

ZurdoDev

Comments

CHill60 21-Jun-19 11:40am

See the select

,ISNULL(SUM(p1.[amount_fc]),0) AS 'paid fc'

:-)

ZurdoDev 21-Jun-19 12:39pm

Ohh. Well, either way, they are doing something wrong that we can't tell without being able to run it.

The HAVING is pretty simple and should work fine.

CHill60 21-Jun-19 13:29pm

Yeah. We'll see if they come back with the required information. I suspect it's a misunderstanding to be honest