database query for 2 level report 1

[daniel999]

Hi,

Any help would be greatly appreciated.

I'm trying to do a compile a report with coldfusion. The report will be in this format.

---------------------------------------------------------
|industry | government | finance | media
country| | | |
---------------------------------------------------------
africa | 5 7 9
belgium| 4 10
china | 10 4
denmark| 4 10
----------------------------------------------------------

this table is a flat table with 3 fields: name, country, industry

I need to do a count to see the breakdown of number of people in each country/industry. Some combination does not have any people in it. May I know the most efficient way to do this type of query as my actual database has about 50,000 records.

I am currently resorting to doing a first loop through all the country and then within the country loop, do a database query loop through all the industry. It works but very inefficient when the records are huge.

The solution may be something very basic so do bear with me. Thank you.

Regards,
Daniel

 

[r937]

your query should be a simple grouping query

select country, industry, count(*)
from yourtable
order by country, industry

producing the "crosstab" layout to show the results is then facilitated by using the GROUP= option of CFOUTPUT

the crosstab processing is tricky, but not insurmountable

i show an example here:

Dynamic multi-level crosstab layouts in ColdFusion

http://r937.com/crosstab.cfm

rudy

 

[pcorreia]

I think there's a typo... shouldn't it be:

  SELECT   country, industry, count(name)
  FROM     yourtable
  GROUP BY country, industry

 

[pcorreia]

Sorry, I see now that there's a reason for doing an ORDER BY, too, since you're doing a <CFOUTPUT GROUP=&quot;&quot;>... but I believe if you don't have a GROUP BY clause, your count(*) will return the same value in each row (a count of the number of records in the table).

So maybe it should be:

  <cfquery name=&quot;qryValues&quot;>
    SELECT   country, industry, count(name)
    FROM     yourtable
    GROUP BY country, industry
    ORDER BY country, industry
  </cfquery>

As mentioned in rudy's (very informative) article, you'd then need to do another query against either the database or the first query to get a list of industries. The display code is a little tricky, but definitely read rudy's article. It makes it a lot clearer.

 

[r937]

yes, an ORDER BY is probably a very good idea -- although most databases will return results in the GROUP BY sequence without one

as for the difference between count(*) and count(name), no, there should not be any difference in this particular example

the difference is that count(*) counts rows, whereas count(name) counts non-null values of the name column

in this example, they should be the same (because it doesn't make sense to have a row with a null name in a particular country/industry)

in any case, both count(*) and count(name) will be returned with different values for each group -- i.e. the number of rows per country/industry

yes, count(*) is often used (without a GROUP BY) to get total rows in a table, but with a GROUP BY, it gets rows per group

rudy

p.s. thanks for the kind words about my article

 

[pcorreia]

I think we're actually in agreement here. I didn't mean to find fault with your use of COUNT(*) as opposed to COUNT(name) -- in retrospect, if I had been paying more attention, I would have been better to keep the COUNT(*) from your example to avoid this type of confusion. I was taking issue with using COUNT(*) in a query with no GROUP BY clause -- in that case, regardless of whether you're counting rows or name values, you'll get a count of all rows, not just the ones in a particular group. I was trying to point out the lack of a GROUP BY clause in your example.

Sorry if I confused anyone -- the difference between COUNT(*) and COUNT(name) is semantic, as long as the &quot;name&quot; column doesn't have any nulls.

 

[r937]

oh, okay, now i see it

DOH! (slaps self on forehead)

of course, my query was wrong, i meant to say GROUP BY

thanks, then

rudy

 

[daniel999]

Thanks guys for your solution. It really helped me.

Regards,
Daniel