HAVING SUM() Issue

Question

I want to add the total sales by date using the HAVING SUM () variable but it is not working as expected.

    SELECT
       sum(SalesA+SalesB) as Sales,
       sum(tax) as tax,
       count(distinct SalesID) as NumOfSales,
       Date
    FROM
       SalesTable
    WHERE
       Date >= '2014-03-01'
    GROUP BY
       Date, SalesA
    HAVING
       sum(SalesA+SalesB) >= 7000
    ORDER BY
       Date

The results are;

   |Sales| tax | NumOfSales | Date |
    10224| 345 |     1      |2014-03-06| 
     9224| 245 |     1      |2014-03-06|
     7224| 145 |     1      |2014-03-06|

If I remove the SalesA in the GROUP BY clause it seems to ignore my HAVING sum clause and adds all the totals.

I would like the results to sum all by date like this .

    |Sales| tax | NumOfSales | Date |
     26672| 735 |     3      |2014-03-06

Thank you for any help you can provide.

http://stackoverflow.com/questions/7434657/how-does-group-by-work The problem is with group by..read this link — Coffee_lover, Mar 10 '14 at 13:46

score 2 · Answer 1 · edited Mar 10 '14 at 14:22

2

You can try rewriting your SQL statement as follows.

SELECT
   sum(SalesA+SalesB) as Sales,
   sum(tax) as tax,
   count(distinct SalesID) as NumOfSales,
   Date
FROM
   SalesTable
WHERE
   Date >= '2014-03-01' AND SalesA+SalesB >= 10000
GROUP BY
   Date
ORDER BY
   Date

edited Mar 10 '14 at 14:22

gpgekko

3,506
3
32
35

answered Mar 10 '14 at 14:00

Abi

174
1
4

score 1 · Answer 2 · answered Mar 10 '14 at 13:44

1

Try with HAVING Sales >= 7000

answered Mar 10 '14 at 13:44

dkasipovic

5,930
1
19
25

It is :), typo when i had entered it here – MLeBlanc Mar 10 '14 at 13:45
Edit your question then to reflect your real query – dkasipovic Mar 10 '14 at 13:45
Also, if you want to group by `Date`, why don't you `GROUP BY Date`, why do you need SalesA column in group (or is that also a part of what you need). You should show an example of your input table. – dkasipovic Mar 10 '14 at 13:46
if I remove the GROUP BY "SalesA" it adds all of the data returning all records Above 7k and below. – MLeBlanc Mar 10 '14 at 13:48
Well if you want to sum only those >= 7000 then you do it in WHERE, and not in HAVING. HAVING is applied only after the aggregate function. Try with `WHERE Date>=.... AND SalesA+SalesB >= 7000` – dkasipovic Mar 10 '14 at 13:56

automatic · Accepted Answer · 2014-03-10T14:06:39.390

1

Do you want to remove individual rows whose salesa + sales b < 7000 so that you only sum rows whose total SalesA + SalesB >= 7000?

SELECT
   sum(SalesA+SalesB) as Sales,
   sum(tax) as tax,
   count(distinct SalesID) as NumOfSales,
   Date
FROM
   SalesTable
WHERE
   Date >= '2014-03-01'
   and
   SalesA+SalesB >= 7000
GROUP BY
   Date
ORDER BY
   Date

edited Mar 10 '14 at 14:06

answered Mar 10 '14 at 13:51

automatic

2,727
3
34
31

Seems too Easy :) It fixed the problem i was having. – MLeBlanc Mar 10 '14 at 13:55
Thanks you all for the help – MLeBlanc Mar 10 '14 at 13:55
@automatic is it correct to use where with count(aggregate function) ??if yes please explain – Coffee_lover Mar 10 '14 at 16:21
shouldn't we use having ? – Coffee_lover Mar 10 '14 at 16:23
1

Aggregates and Having are fine, but in this case the data needed to be removed from the aggregate be for the aggregate was performed rather than removing a particular set out of the aggregate results – automatic Mar 10 '14 at 19:08

HAVING SUM() Issue

3 Answers3