CODEWITHSUNDEEP
sqldatabasegroup-byinner-joinwhere-clause
shahid khan
shahid khan

Reputation: 439

use inner join where and group by in same query

I want select set of data from database to finding sum of column based on other columns value and also want to join two tables and these values are between two dates can i do in single query it please help me to know what i did so far is

string sql1 = @"select 
    Ledger.LedId,
    Ledger.LedName,
    sum(adjmnt.OpenBalanceAmount) as Totaldebit, 
    (adjmnt.OpenBalanceVocherType) as adjtype 
from  
    Ledger  inner join Adjustment adjust on
        Ledger.LedId =     adjmnt.LedId 
where  
    Ledger.Date >= @prmStartDate
    and Ledger.Date <= @prmEndDate";

what I am looking for to do is

string sql1 = @"select
    Ledger.LedId,
    Ledger.LedName,
    sum(adjmnt.OpenBalanceAmount) as Totaldebit , 
    (adjmnt.OpenBalanceVocherType) as adjtype 
from  
    Ledger  inner join Adjustment adjust on 
        Ledger.LedId= adjmnt.LedId 

groupby
    (adjmnt.OpenBalanceVocherType) 

where 
    Ledger.Date >= @prmStartDate and
    Ledger.Date <= @prmEndDate";

but when i trying to do this i get

incorrect syntax near where

please help me

Upvotes: 0

Views: 2466

Answers (2)

Mustapha Larhrouch
Mustapha Larhrouch

Reputation: 3393

You need to put group by after where and group by Ledger.LedId,Ledger.LedName :

string sql1 = @"select Ledger.LedId,Ledger.LedName,sum(adjmnt.OpenBalanceAmount) 
Totaldebit ,(adjmnt.OpenBalanceVocherType) as adjtype 
from  Ledger  inner join Adjustment adjmnt on 
Ledger.LedId= adjmnt.LedId
where Ledger.Date >= @prmStartDate and Ledger.Date <= @prmEndDate
group by Ledger.LedId,Ledger.LedName";

http://www.w3schools.com/sql/sql_groupby.asp

Upvotes: 2

Ludricio
Ludricio

Reputation: 156

I've added a suggestion for formatting to your question.

And when looking at the code in a more structured way, you can instantly tell that there is an error where groupby should in fact be group by.

And as Mostafa mentioned, the group by clause should be placed after the where clause.

I don't know if that fixes your problem, but it is surely an error.

Upvotes: 1

Related Questions