SQL SUM HAVING Problem

[Pendragon]

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;

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]

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.

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

[NeoPa]

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!

[Pendragon]

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.

[NeoPa]

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.

[Pendragon]

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.

[gershwyn]

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.

[NeoPa]

@Pendragon
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.

[ck9663]

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

Good Luck!!!

~~ CK

[Pendragon]

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

[Pendragon]

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;

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.

[NeoPa]

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.