SQL - Unsing SUM and WHERE several times in the same querey (1 Viewer)

bobunknown · Dec 7, 2018

Hi all,

I Have a database for bin collections and I need to know the total number of collections for each bin. The code below works and return the sum of all collections for all bins with a Size ID of 1.

Code:

SELECT DISTINCT SUM (Collections) AS Total120
FROM Bins
WHERE (((Bins.[Bin Size ID])=1));

However when I try to add the code below, to see the sum of collections for bins with a size ID of 2 I get a syntax error

Code:

SELECT DISTINCT SUM (Collections) AS Total120
FROM Bins
WHERE (((Bins.[Bin Size ID])=1)), 
SELECT SUM (Collections) AS Total140
FROM Bins
WHERE (((Bins.[Bin Size ID])=2));

This is odd as the following code works perfectly to count the total number of bins that we have out. I have tried to follow it as near as possible to get the answer but I cant understand why there is such a difference between the was SUM and COUNT are formatted.

Code:

SELECT DISTINCT (SELECT COUNT(*)
From Bins
WHERE ([Bin Size ID] = 1)
) AS Total_120, (SELECT COUNT(*)
From Bins
WHERE ([Bin Size ID] = 2)
) AS Total_140, (SELECT COUNT(*)
From Bins
WHERE ([Bin Size ID] = 3)
) AS Total_240, (SELECT COUNT(*)
From Bins
WHERE ([Bin Size ID] = 4)
) AS Total_360, (SELECT COUNT(*)
From Bins
WHERE ([Bin Size ID] = 5)
) AS Total_660, (SELECT COUNT(*)
From Bins
WHERE ([Bin Size ID] = 6) OR ([Bin Size ID] = 7)
) AS Total_1100
FROM Bins;

Any help would be appreciated.

CJ_London · Dec 7, 2018

because you are using sub queries in your last query

not sure why you don't just use a normal groupby query

Code:

SELECT[Bin Size ID], SUM (Collections) AS TotalCollections
FROM Bins
GROUP BY [Bin Size ID]

bobunknown · Dec 7, 2018

aaaaaand its that simple

That works, I've not had any previous experience using group by queries, something ill look in to now.

But just as a further question there are 14 total bin ID's and im only interested in 1-7 (and having 6+7) combined.

would it be as simple as SELECT([Bin Size ID] = 1)

or would the alterations need to be made to the GROUP BY statement?

bobunknown · Dec 7, 2018

Code:

SELECT Bins.[Bin Size ID], Sum(Bins.Collections) AS TotalCollections
FROM Bins
GROUP BY Bins.[Bin Size ID]
HAVING (((Bins.[Bin Size ID])=1)) OR (((Bins.[Bin Size ID])=2)) OR (((Bins.[Bin Size ID])=3)) OR (((Bins.[Bin Size ID])=4)) OR (((Bins.[Bin Size ID])=5)) OR (((Bins.[Bin Size ID])=6)) OR (((Bins.[Bin Size ID])=7));

The above specifies the Bin Size ID now all I need to do is combine ID 6 & 7's sum...

Minty · Dec 7, 2018

CJ's offline - but initially try this

Code:

SELECT[Bin Size ID], SUM (Collections) AS TotalCollections
FROM Bins
Where [Bin Size ID] <8 
GROUP BY [Bin Size ID]

To simplify your query. Or another method

Code:

SELECT[Bin Size ID], SUM (Collections) AS TotalCollections
FROM Bins
Where [Bin Size ID] In(1,2,3,4,5,6,7)
GROUP BY [Bin Size ID]

CJ_London · Dec 7, 2018

all I need to do is combine ID 6 & 7's

nothing like throwing in a last minute requirement

try

SELECT iif([Bin Size ID] in (6,7),67,[bin Size ID]) as Size, SUM (Collections) AS TotalCollections
FROM Bins
Where [Bin Size ID] In(1,2,3,4,5,6,7)
GROUP BY iif([Bin Size ID] in (6,7),67,[bin Size ID])

bobunknown · Dec 7, 2018

That works a treat dude

interesting to see how you can abbreviate and it will still understand. im gonna do some experimenting to understand it better.

One last question regarding

Code:

GROUP BY iif([Bin Size ID] in (6,7),67,[bin Size ID])

specifically 'iif' why the extra I? why not just GROUP BY IF?

Also after you define (6,7) why dose it need to be repeated as 67?

Just trying to understand all the rules around abbreviation etc.

Thanks for all the help and the speedy reply's.

Minty · Dec 7, 2018

CJ_London said:
nothing like throwing in a last minute requirement

It is Friday!

plog · Dec 7, 2018

GROUP BY iif([Bin Size ID] in (6,7),67,[bin Size ID])

IIF is how Access SQL does if. If means nothing inside an Access query.

It's not repeated, its what's returned when the first part of the IIf is true. Here's how an IIf works (https://www.techonthenet.com/access/functions/advanced/iif.php):

IIF(A, B, C)

A is the condition that resolves to True or False
B is what is returned when A is true
C is what is returned when A is false.

The 67 does not need to be repeated. It specifically doesn't even have to be there. As above shows, it's what is returned if A is true. The "...in (6,7)" has to be there because that's the logic that drives the whole thing. You can change the 67 to whatever you want--just be sure to change any corresponding statement in the SELECT as well.

bobunknown · Dec 7, 2018

You make it sound so simple.

Ill get to reading up

Thanks again, have a good weekend

SQL - Unsing SUM and WHERE several times in the same querey (1 Viewer)

bobunknown

Registered User.

CJ_London

Super Moderator

bobunknown

Registered User.

bobunknown

Registered User.

Minty

AWF VIP

CJ_London

Super Moderator

bobunknown

Registered User.

Minty

AWF VIP

plog

Banishment Pending

bobunknown

Registered User.

Similar threads

Users who are viewing this thread