Skip to main content
A
Anonymous
February 3, 2010
Answered

help with sql query

  • February 3, 2010
  • 4 replies
  • 555 views

everything looks right to me, but i'm not getting the results I want.  I want to have a list of all categories from table 1 and a count number based on table 2.  Right now I'm only getting returned the categories that are not null.

i.e. i want

category 1 - 50

category 2 - 50

category 3 - 0

category 4 - 50

etc.

right now I'm getting:

category 1 - 50

category 2 - 50

category 4 - 50

etc.

"SELECT table1.categoryName, ISNULL(COUNT(table2.id), 0) AS TotalCategories FROM table1 LEFT OUTER JOIN table2 ON table1.categoryName = table2.category WHERE (table2.expireDate >= GETDATE()) GROUP BY table1.categoryName ORDER BY TotalCategories DESC"

I'd settle for just that answer, but if you're feeling extra helpful today:

once the query is doing what i need...  i then would like to put this info into a string of dynamic text like so

50,50,0,50

(if you haven't guessed, i'm building a pie chart using google charts api).

Thank you in advance for an answer to my oversight.

This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer jon8

SELECT t1.categoryName, ISNULL(COUNT(t2.id), 0) AS TotalCategories

FROM table1 t1

LEFT JOIN table2 t2 ON t1.categoryName = t2.category AND t2.expireDate >= GETDATE()

GROUP BY t1.categoryName

ORDER BY TotalCategories DESC

4 replies

B
bregent
Participating Frequently
February 3, 2010

You are not using a group by clause so count() is counting all rows.

A
Anonymous
February 3, 2010

can you show what you're suggesting?  i want it to count all rows, and there is a group by, so I'm not sure I understand your response.

i want to list all categories from table 1 (including those that don't exist in table 2), and provide the corresponding count from table 2 for all that do exist as well.

i.e.

i'm getting this right now with the above sql:

category 1 - 50

category 2 - 50

category 4 - 50

but what I want is this:

category 1 - 50

category 2 - 50

category 3 - 0   (this category doesn't exist in table 2)

category 4 - 50

jon8
jon8Correct answer
Inspiring
February 3, 2010

SELECT t1.categoryName, ISNULL(COUNT(t2.id), 0) AS TotalCategories

FROM table1 t1

LEFT JOIN table2 t2 ON t1.categoryName = t2.category AND t2.expireDate >= GETDATE()

GROUP BY t1.categoryName

ORDER BY TotalCategories DESC

Firefly Community Gallery
Adobe Firefly

Remix with Firefly Community Gallery

Thousands of free creations to fall in love with and remix in Firefly.

Explore now
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices