[thelist] case statement catch-all? MSSQL

Jay Blanchard jay.blanchard at niicommunications.com
Wed Aug 24 09:03:53 CDT 2005


[snip]
There are 3 sites listed in the table; char(1)-  C,P, and T for the 
above states.  There are no nulls (if there are, thats another issue :p
)

I need the following results:

CT | PA | TX | ALL 3 SITES COMBINED/OVERALL
---------------------------------------------
  %    %    %              %

the percent and all that good stuff I can take care of...  i'm just not 
sure how to grab each seperately by the site field, and then basically 
disregard the site field and take everything
[/snip]

Perhaps a crosstab query; (bull-in-the-china-shop-method)

SELECT 
SUM(IF(CT = 'C', 1, 0)) AS "CT",
SUM(IF(PA = 'P', 1, 0)) AS "PA",
SUM(IF(TX = 'T', 1, 0)) AS "TX",
SUM((IF(CT = 'C', 1, 0)))+IF(PA = 'P', 1, 0))+IF(TX = 'T', 1, 0))) AS
"ALL"
FROM table
GROUP BY whatever you see necessary


More information about the thelist mailing list