Skip to main content
A
Anonymous
February 3, 2010
解決済み

help with sql query

  • February 3, 2010
  • 返信数 4.
  • 555 ビュー

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.

このトピックへの返信は締め切られました。
解決に役立った回答 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

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
jon8解決！
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

契約条件Accessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices