Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163

    Unanswered: Return Count instead of Rows

    The following query returns 2142 rows which is correct.

    Code:
          select COUNT(*), sum(v.currentMarket)
            from TRMaster m
           inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
           inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
           where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
           group by m.Year,  m.Parcel
           having SUM(v.currentmarket) > 0
    How can I convert this query so that it returns just the count of 2142?

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try to remove the Group By.

    Hope this helps.

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    No, that did not work. That returns the wrong count. I'm not sure which rows are being incorrectly added since the row count jumps to over 3K

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Easiest would be to slap a select count(*) around it like this, but there are more elegant ways:
    Code:
    select count(*) from
       (select COUNT(*) as num, sum(v.currentMarket) as total
            from TRMaster m
           inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
           inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
           where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
           group by m.Year,  m.Parcel
           having SUM(v.currentmarket) > 0) a

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    That worked.

    I had tried something like this before posting but I omitted the alias so all I got was a not very helpful syntax error.

    Thank-you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •