Post reply

How to write query for following problem

  • January 5, 2010 at 12:10 am

    #94231

    I have a table like that, which information about user's transaction made by them.

    Name city Email Amount

    amit chd amit@amit.com 600

    abc zyxaz aaadff@as.com 14455

    abchvjhzbbyxazaaadff@as.com 500

    abchvjhzbbyxazaaadff@as.com 550

    abchvjhzbbyxazaaad@as.com 550

    abchvzbbyxazaaad@as.com 550

    abchvzbbyxazaaa@as.com 550

    abch zbbyxazaaa@as.com 550

    amit chd amit@amit.com 700

    amit chd amit@amit.com 800

    amit chd amit@amit.com 1200

    amit chd amit@amit.com 80

    amit chd amit@amit.com 200

    now I want to calculate the sum of all transaction which are more than 500, having same email id.

    so i used query

    select name,city,email from tb1 where amount> 500 group by(email) in mysql

    Name city Email Amount

    abchvjhzbbyxazaaadff@as.com 1100

    abchvzbbyxazaaad@as.com 1100

    abc zyxaz aaadff@as.com 15005

    amit chd amit@amit.com 3300

    there its working well as per requirement but when i used same query in ms sql server then its not working there then can any one tell me equivalent query for sql server?

    please help me

  • January 5, 2010 at 12:33 am

    #1098775

    select name,city,email as amt from tb1 where amtount> 500

    group by name,city,email

  • January 5, 2010 at 12:35 am

    #1098777

    or may be this?

    select name,city,email, sum(amount) as amt

    from tb1

    group by name,city,email

    Having sum(amount) > 500

    ---------------------------------------------------------------------------------

  • January 5, 2010 at 1:15 am

    #1098786

    now I want to calculate the sum of all transaction which are more than 500, having same email id.

    Name city Email Amount

    abchvjhzbbyxazaaadff@as.com 1100

    abchvzbbyxazaaad@as.com 1100

    abc zyxaz aaadff@as.com 15005

    amit chd amit@amit.com 3300

    create table #temp

    (

    Name1 varchar(20),

    city varchar(20),

    Email varchar(20),

    Amount int

    )

    insert into #temp

    select 'amit','chd','amit@amit.com',600

    union all

    select 'abc','zyxaz','aaadff@as.com',14455

    union all

    select 'abchvjh','zbbyxaz','aaadff@as.com',500

    union all

    select 'abchvjh','zbbyxaz','aaadff@as.com',550

    union all

    select 'abchvjh','zbbyxaz','aaad@as.com',550

    union all

    select 'abchv','zbbyxaz','aaad@as.com',550

    union all

    select 'abchv','zbbyxaz','aaa@as.com',550

    union all

    select 'abch','zbbyxaz','aaa@as.com',550

    union all

    select 'amit','chd','amit@amit.com',700

    union all

    select 'amit','chd','amit@amit.com',800

    union all

    select 'amit','chd','amit@amit.com',1200

    union all

    select 'amit','chd','amit@amit.com',80

    union all

    select 'amit','chd','amit@amit.com',200

    01)

    select min(Name1)Name1,min(city) city,Email,sum( Amount)Amount

    from #temp

    where Amount > 500

    group by Email

    02)

    select max(Name1)Name1,max(city) city,Email,sum( Amount)Amount

    from #temp

    where Amount > 500

    group by Email

  • January 5, 2010 at 3:05 am

    #1098811

    But i want to group by only with email, because some time names are different but emails are same so i need to result according to group of email. so please tell me if there is any way

  • January 5, 2010 at 3:12 am

    #1098817

    then use MIN(Name) or MAX(Name) , your choice, to return a single 'name',

    when you use GROUP BY, all returned columns have to be aggregate functions of contained in the GROUP BY list


    Clear Sky SQL
    My Blog[/url]

  • January 5, 2010 at 3:15 am

    #1098819

    amitsingh308 (1/5/2010)

    But i want to group by only with email, because some time names are different but emails are same so i need to result according to group of email. so please tell me if there is any way

    Hi,

    Ok, are you tried the output with what I post early?

  • January 5, 2010 at 4:35 am

    #1098849

    Ya its work thanx sir

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply