SQL - Multiple COUNT in a single query

[reefoid]

Hi

I'm trying to create a query that requires multiple COUNT columns in a single query. Basically I have a table with ticket dates in it and I want to display results showing company names, total tickets for year and total tickets per week.

For now, just to keep it simple, I'm only coding for the company names, year total and a single week. So far I have this code:

SELECT dmhelp.contid AS company, 
(Select Count(contid) 
FROM dmhelp dmhelp
WHERE dmhelp.calldate>{d '2010-12-31'}) 
AS yeartotal,
(Select Count(contid) 
FROM dmhelp dmhelp
WHERE dmhelp.calldate>{d '2010-12-31'}
AND WEEK(dmhelp.calldate) = 2)
AS week02
FROM dmhelp
GROUP BY dmhelp.contid

This returns the company name, but the the other two columns are showing the totals for ALL companies, rather than the company total on each line. Not sure if that is clear, so to clarify there are currently 2,900 records for all companies over the year, and 84 records for all companies in week 2, so the query above is returning:

Company 1, 2900, 84
Company 2, 2900, 84
Company 3, 2900, 84

So somehow I need to filter it to show only the relevant totals for each company. I've tried adding 'AND dmhelp.contid = company' into each of the sub-select statements but this throws an error that column 'company' is not known.

Can anyone advise how I can use the record returned in column 1 to filter the sub-select statements.

Thanks.

 

[shine]

Use SUM on the total commands and group by the company ID, you don't need to use count.

 

[visibleman]

It'll throw that error as 'Company' is an alias, try HAVING instead of WHERE. I'd also stop using the same table aliases on each sub-select and change the aliases to something other than the table name, ie: A, B, C.
Also try the SUM function instead of COUNT.

Edit - Beaten by shine

 

[reefoid]

Thanks guys, I'll try the HAVING clause when I get back to the office later.

With regards to SUM, its a non-numeric column I'm counting so surely SUM won't work here?

 

[cotton31]

Won't this work?

SELECT dmhelp.contid AS company
, Count(contid) AS yeartotal
, SUM(CASE WHEN WEEK(dmhelp.calldate) = 2) THEN 1 ELSE 0 END) AS week02

FROM dmhelp

WHERE dmhelp.calldate>{d '2010-12-31'}

GROUP BY dmhelp.contid