SQL SUM HAVING Problem

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Pendragon
    New Member
    • Feb 2010
    • 5

    SQL SUM HAVING Problem

    Hi,

    I'm trying to write a query, which produces a list of users, whose telephone call costs total over 15.00 for the month, but I'm having difficulty. Here's my query;

    Code:
    SELECT     userid, username, SUM(netamt)
    FROM         EMNCommon
    HAVING      (SUM(netamt) >= '15.0000') AND (transdate > GETDATE() - 30)
    GROUP BY username
    I've tried a few different variations of this, but I'm not getting anywhere. Any help would be much appreciated. Thanks in advance.
  • gershwyn
    New Member
    • Feb 2010
    • 122

    #2
    What problem are you having specifically?

    I think you want transdate > getdate() - 30 in a where clause, not a having clause. You want to restrict the records by date before performing any calculations. Also, the fact that you're including userid but not using it in a group by clause or in an aggregate function might be causing a problem as well.

    Finally, you have '15.0000' in quotes, but the sum function is going to return a number. Comparing a number to a string like that results in a data type mismatch error for me.

    Try this, and let us know if you're still running into problems.

    Code:
    SELECT userid, username, SUM(netamt)
    FROM EMNCommon
    WHERE transdate > GETDATE() - 30
    GROUP BY userid, username
    HAVING sum(netamt) >= 15;

    Comment

    • NeoPa
      Recognized Expert Moderator MVP
      • Oct 2006
      • 32638

      #3
      Nice answer Gershwyn :) You got to the bottom of all the issues I saw, and some I missed.

      That should solve pretty well all your problems Pendragon.

      Welcome to Bytes!

      Comment

      • Pendragon
        New Member
        • Feb 2010
        • 5

        #4
        Thanks for your help guys. That worked a treat. Just need to do some research on how to automate the creation of that report and email it to a user every week. I was thinking of something along the lines of creating a text .sql osql file, a scheduled task and using our own SMTP utility. However it might be easier creating a SQL job and I think SQL has its own in-built email function from a quick search of Help. Just don't have the permissions to check right now.

        Thanks again. Much appreciated.

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32638

          #5
          Sounds like you're using MS SQL Server 2000. I know that supports sending notification emails but I haven't seen what it can do on that score from within T-SQL itself. Maybe post for some help in the SQL Server forum. Good luck.

          Comment

          • Pendragon
            New Member
            • Feb 2010
            • 5

            #6
            I'm actually using SQL Server 2005, which from what I've read has got better email functionality than previous versions of SQL.

            Sorry I've been posting in the wrong forum. Doing a quick search I saw a few SQL questions posted in this Access forum and assumed this was the forum for such posts. I didn't see a SQL forum. Thanks for not flaming me, as I know some sites can be quite militant about such things.

            Comment

            • gershwyn
              New Member
              • Feb 2010
              • 122

              #7
              No worries. I had wondered it this might be something other than Access (which doesn't have a getdate() function.) You're much more likely get an answer to your automation question in a different forum, though. I can help with SQL queries but I'm no help at all with what you're asking.

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32638

                #8
                Originally posted by Pendragon
                Thanks for not flaming me, as I know some sites can be quite militant about such things.
                Flaming?!? Gentle guidance I'm sure :D

                I hadn't even realised it wasn't Access. As Gershwyn says, we can help to a certain extent (I think in this case it was simple generic SQL), but otherwise it would need to be moved across. I'll move it across anyway now I know, as it is more likely to be found by searchers if in the correct forum.

                Comment

                • ck9663
                  Recognized Expert Specialist
                  • Jun 2007
                  • 2878

                  #9
                  I created some applications that require email notification using sql 2005. This is a good place to start.

                  Good Luck!!!

                  ~~ CK

                  Comment

                  • Pendragon
                    New Member
                    • Feb 2010
                    • 5

                    #10
                    Thanks for the link ck9663, it's definitely been of help.

                    Comment

                    • Pendragon
                      New Member
                      • Feb 2010
                      • 5

                      #11
                      Thanks for helping me produce the query that was originally required. I've been asked to tweak the report by itemising calls by username, for all users who have made telephone calls totalling over 15.00, rather than producing a report grouping and totalling the cost by username. I thought it was a simple case of removing the 'GROUP BY' line or removing 'SUM' from the select line, but different combinations aren't getting me anywhere and I'm a bit out of my SQL depth now. Here's the original query;

                      Code:
                      SELECT     userid, username, SUM(cost)
                      FROM         DATABASE
                      where (account LIKE 'N%') AND (type ='phone') AND (date >GETDATE()-30) AND (unitid IN ('phone','cell'))
                      GROUP BY username, userid
                      HAVING (SUM(cost)>=15)
                      ORDER BY username
                      Any assistance would be much appreciated.

                      Comment

                      • NeoPa
                        Recognized Expert Moderator MVP
                        • Oct 2006
                        • 32638

                        #12
                        For that you need to use a Subquery (See Subqueries in SQL).

                        The original query would be linked into the original source data (as a Subquery), so you could list all items, but only those where their totals exceed 15.00 currency units.

                        Comment

                        Working...