Wayne Barron
asked on
Select statement with COUNT (SQL Server) and return records that have 0 count as well
Hello All;
OK, I have a basic layout.
The only issue is there are over 1,000 records and it takes a while to load.
Is there a way to have everything in 1 SQL Query?
This is what I have tried.
(This is the actual SQL Table(s) code)
The issue with this is that all the area's that have no count are all showing as "1" ( ONE )
I need them to show as ( 0 ), instead of ( 1 )
and have ALL records returned back, just as the Query does, but with the accurate count which includes the 0 count fields.
some1 = 3
some2 = 5
some5 = 0
some9 = 10
some12 = 0
some13 = 1
some14 = 2
Thanks All
Carrzkiss
OK, I have a basic layout.
select mainname, mainid from table
while not rs.eof
select count(subID) as IDs from subtable where mainid=?
sqlCt.CreateParameter("@MainID", 3,1, , rs("MainID"))
' Display the records
<%=rs("mainname")%> (<%rsCt("IDs")%>)
rs.movenext
wend
The only issue is there are over 1,000 records and it takes a while to load.
Is there a way to have everything in 1 SQL Query?
This is what I have tried.
(This is the actual SQL Table(s) code)
The issue with this is that all the area's that have no count are all showing as "1" ( ONE )
I need them to show as ( 0 ), instead of ( 1 )
and have ALL records returned back, just as the Query does, but with the accurate count which includes the 0 count fields.
some1 = 3
some2 = 5
some5 = 0
some9 = 10
some12 = 0
some13 = 1
some14 = 2
SELECT Zones.ZoneID, Zones.ZoneName, SubCategories.SubName, COUNT(Zones.ZoneID) AS Questions
FROM Zones LEFT OUTER JOIN
SubCategories ON Zones.SCID = SubCategories.SCID LEFT OUTER JOIN
Media ON Zones.ZoneID = Media.ZoneID
WHERE (NOT (Zones.ZoneName LIKE '%New Zone%'))
GROUP BY ALL Zones.ZoneID, Zones.ZoneName, SubCategories.SubName
Thanks All
Carrzkiss
ASKER
still returns (1) where there should be a (0)
ASKER
You counted the SCID, that is the wrong count.
The ZoneID is what needs to be counted.
For some reason, the records that are listed in the "Where" clause, and NOT.
Are showing up, and when they show up, they are showing (0) beside them.
All the other records are showing (1).
The ZoneID is what needs to be counted.
For some reason, the records that are listed in the "Where" clause, and NOT.
Are showing up, and when they show up, they are showing (0) beside them.
All the other records are showing (1).
We NEED sample data otherwise we will go in circles like this
NOT a massive amount of data is needed (please!) just a "representative set"
NOT a massive amount of data is needed (please!) just a "representative set"
ASKER
I did a simple count.
And this one is resulting in (0)
However, it will result in (1) when it is in with the BIG Query
SELECT COUNT(ZoneID) AS Questions
FROM Media
where (ZoneID = 4)
And this one is resulting in (0)
However, it will result in (1) when it is in with the BIG Query
>>"You counted the SCID, that is the wrong count. The ZoneID is what needs to be counted."
Unless you are counting DISTINCT it will not matter which non-null column you count, but it DOES MATTER which TABLE you count from.
I repeat, we really need a good set of sample data to help, otherwise we are flying blind.
Unless you are counting DISTINCT it will not matter which non-null column you count, but it DOES MATTER which TABLE you count from.
I repeat, we really need a good set of sample data to help, otherwise we are flying blind.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ZoneID ZoneName SubName Questions
1 Parade Event 3
2 Carnival Event 8
3 Charity Event 1
4 Playground Activity 0
Hopefully, that will help out.
the last record has (0) questions asked, so it should show (0)
In my case, in the Query I provided, it shows (1)
1 Parade Event 3
2 Carnival Event 8
3 Charity Event 1
4 Playground Activity 0
Hopefully, that will help out.
the last record has (0) questions asked, so it should show (0)
In my case, in the Query I provided, it shows (1)
ASKER
THANK YOU!!!!!!!
That did it PortletPaul.
You Rock my friend.
Have a rocking rest of the weekend.
Wayne
That did it PortletPaul.
You Rock my friend.
Have a rocking rest of the weekend.
Wayne
Cheers. Thanks. You also enjoy that weekend.
ASKER
It took me a few looks to catch on to it.
I needed to connect to the Media.ZoneID, and not the Zones.ZoneID.
I cannot believe I overlooked that one.
I needed to connect to the Media.ZoneID, and not the Zones.ZoneID.
I cannot believe I overlooked that one.
yep, to get any zeros from COUNT() you need NULLs
best place for NULLs is an outer joined table
in the end, it was a matter of me making a guess as to which one
best place for NULLs is an outer joined table
in the end, it was a matter of me making a guess as to which one
ASKER
It was a good guess.
Sorry that I did not catch onto it myself.
I guess sometimes you just need to step back, which is what I usually do, and see what fits better.
After putting all the (counts) into the SQL Statement, and now just having "1" statement.
The page loads all records in less than a second, compared to the 5 seconds that it took before.
Have a good one everyone.
Wayne
Sorry that I did not catch onto it myself.
I guess sometimes you just need to step back, which is what I usually do, and see what fits better.
After putting all the (counts) into the SQL Statement, and now just having "1" statement.
The page loads all records in less than a second, compared to the 5 seconds that it took before.
Have a good one everyone.
Wayne
Open in new window
{+ edit} In your code the FROM table is Zones, and you are counting values fro that same table, that is why you got 1 (because there has to be a row in zones for the query to return a row in the result). note too that the count() function increments by one for every non-null value.By counting from one of the left joined tables you might encounter nulls, and because the count() function does not increment on nulls then you can get a zero result from the count.
What I cannot tell you - because I cannot see any of your data is which table to count from
(note also that you used "table" and "subtable" in the loop but not in the query, so hard to match hese together)