SQL Function & groupd by question 3

[vpekulas]

Hello (please help
What I need is to connect the following 2 queries into one,
but I need to display even those records that have a FILE_COUNT
bigger then 0. For some reason it displays records only with
FILE_COUNT > then 1.


SELECT TABLE_CATE.ID, TABLE_CATE.fldNAME, COUNT(TABLE_FILE.ID) AS FILE_COUNT
FROM TABLE_CATE, TABLE_FILE
WHERE TABLE_FILEfldCATE_ID = TABLE_CATE.ID
ORDER BY TABLE_CATE.fldORDER ASC


Thanks for your time !

http://www.Europeum.net

 

[tlbroadbent]

I'm unclear about your request. Does the following provide the result you seek?

SELECT
TC.ID,
TC.fldNAME,
FILE_COUNT=ISNULL(QRY.FILE_CNT, 0)
FROM TABLE_CATE As TC
LEFT JOIN
(SELECT fldCATE_ID, COUNT(*) AS FILE_CNT
FROM TABLE_FILE GROUP BY fldCATE_ID) As QRY
ON TC.ID=QRY.fldCATE_ID
ORDER BY TABLE_CATE.fldORDER ASC Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[vpekulas]

Not really see, no matter what I do the query generates results only on records that have FILE_COUNT > then 1. When
there are no records (in TABLE_FILE) connected to category
the category names won't show up in results. I need to display ALL category names (TABLE_CATEGORY), even those without any connection to TABLE_FILE.

http://www.Europeum.net

 

[r937]

SELECT TABLE_CATE.ID, TABLE_CATE.fldNAME
, TABLE_CATE.fldORDER
, COUNT(TABLE_FILE.ID) AS FILE_COUNT
FROM TABLE_CATE
LEFT JOIN TABLE_FILE
ON TABLE_CATE.ID = TABLE_FILEfldCATE_ID
GROUP BY TABLE_CATE.ID, TABLE_CATE.fldNAME
, TABLE_CATE.fldORDER
ORDER BY TABLE_CATE.fldORDER ASC

i added TABLE_CATE.fldORDER to the SELECT list because, if i recall, some databases will not let you order by a column you aren't selecting

therefore i had to add it to the GROUP BY as well

in a simple grouped left join, COUNT(TABLE_FILE.ID) will return 0 where appropriate

terry, what's the advantage of a grouped subquery?

and is ISNULL() ansi sql?

and even if it is, shouldn't the syntax be

ISNULL(QRY.FILE_CNT, 0) AS FILE_COUNT

rather than

FILE_COUNT=ISNULL(QRY.FILE_CNT, 0)

(a gentle dig)


rudy

 

[tlbroadbent]

rudy,

"what's the advantage of a grouped subquery?"

1) Columns from Table_Cate, such as fldORDER, can be added to the select list without being added to the Group By clause.

2) Multiple occurences of ID in Table_Cate can result in higher counts than the actual row counts in Table_File if aggregation occurs after the JOIN operation. However, if ID is a primary key that is not an issue in this case.

3) It is a personal preference. I find that using a sub-query makes the SQL script simpler and easier to understand most of the time. You can tell at a glance what is being summarized or counted.

"is ISNULL() ansi sql?"
"shouldn't the syntax be
ISNULL(QRY.FILE_CNT, 0) AS FILE_COUNT."

IsNull is not ANSI SQL so far as I know and you are right about the syntax for aliases. Thanks for the correction. I use T-SQL in MS SQL Server so it's liable to sneak in anywhere. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[dnoeth]

"is ISNULL() ansi sql?"

No, but
COALESCE(QRY.FILE_CNT, 0)

It's just an abbreviation for
CASE
WHEN QRY.FILE_CNT IS NULL THEN 0
ELSE QRY.FILE_CNT
END

And you can use more than two columns, e.g.
COALESCE(this,that,another,0)

btw, COALESCE is also supported by MS SQL Server, so there's really no need to use the proprietary ISNULL

Dieter

 

[tlbroadbent]

Thanks Dieter. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[vpekulas]

Thanks guys. Very much appreciate it.

http://www.Europeum.net