Counting Values that are in one column of SQL

I have a Column called Quote in a table called NewBusinessLineItem.. There are 4 possible responses, Yes, No, Drop or Self. I would like to count the responses into 4 columns.

Yes Total       No Total        Drop Total      Self Total
   45                16                    8                     47
Michael FranzCFOAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
<air code, rename the obvious>

SELECT
  Count(CASE WHEN Quote='Yes' THEN 1 ELSE 0 END) as 'yes_total',
  Count(CASE WHEN Quote='No' THEN 1 ELSE 0 END) as 'No_total',
  Count(CASE WHEN Quote='Drop' THEN 1 ELSE 0 END) as 'drop_total',
  Count(CASE WHEN Quote='Self' THEN 1 ELSE 0 END) as 'self_total'
FROM NewBusinessLineItem
0
 
Surendra NathConnect With a Mentor Technology LeadCommented:
try the below one, this is one possible way of achieving your goal, although there is another functionality called pivot which can be employed to do this as well.

select Count(case quote when 'Yes' THEN 1 ELSE 0 END) AS 'YES TOTAL'
,Count(case quote when 'NO' THEN 1 ELSE 0 END) AS 'No TOTAL'
,Count(case quote when 'Drop' THEN 1 ELSE 0 END) AS 'Drop TOTAL'
,Count(case quote when 'Self' THEN 1 ELSE 0 END) AS 'Self TOTAL'
from NewBusinessLineItem

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw the above is the SQL Server answer.  Since I see the Access zone in the question, but not whether you are looking for a SQL Server or an Access answer, I'll defer to my Access peers if that's what you're looking for.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
You need to use "Sum" instead of "Count" in the above code.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Bah, need more coffee.

Scott is correct, Sum handles the 1's and 0's, whereas Count would count them both.
0
 
Michael FranzCFOAuthor Commented:
Actually, I have the option to do it in either place. Does not matter. It get imbedded into additional software.



btw the above is the SQL Server answer.  Since I see the Access zone in the question, but not whether you are looking for a SQL Server or an Access answer, I'll defer to my Access peers if that's what you're looking for.
0
 
Michael FranzCFOAuthor Commented:
Sorry,,,, way New to this. Not a programmer..... change SUM how??
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes.  I'll repeat the T-SQL, Scott should get partial credit in this deal...

SELECT
  Sum(CASE WHEN Quote='Yes' THEN 1 ELSE 0 END) as 'yes_total',
  Sum(CASE WHEN Quote='No' THEN 1 ELSE 0 END) as 'No_total',
  Sum(CASE WHEN Quote='Drop' THEN 1 ELSE 0 END) as 'drop_total',
  Sum(CASE WHEN Quote='Self' THEN 1 ELSE 0 END) as 'self_total'
FROM NewBusinessLineItem

>Actually, I have the option to do it in either place.
Do it in SQL then.
0
 
Michael FranzCFOAuthor Commented:
That is WORKING !!!! YEA! So how do I split the credit
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Good deal.

<my opinion>

Equally between the first two comments, which were correct except for count/sum and a minute a part, and ScottPletcher's comment with the Sum catch, would be fine.
0
 
Michael FranzCFOAuthor Commented:
I did forget to ask, how to do a column that has the total in it. is it something like this...

 Sum(CASE WHEN Quote <> " " THEN 1 ELSE 0 END) as 'Total Quoted'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just add it to the SELECT clause.
Keep in mind if there is any value other than Yes, No, Drop, or Self (like 'Banana') then the total will not match the sum of the rest.

SELECT
  Sum(CASE WHEN Quote='Yes' THEN 1 ELSE 0 END) as 'yes_total',
  Sum(CASE WHEN Quote='No' THEN 1 ELSE 0 END) as 'No_total',
  Sum(CASE WHEN Quote='Drop' THEN 1 ELSE 0 END) as 'drop_total',
  Sum(CASE WHEN Quote='Self' THEN 1 ELSE 0 END) as 'self_total',
  Sum(CASE WHEN Quote <> " " THEN 1 ELSE 0 END) as 'Total Quoted'
FROM NewBusinessLineItem
0
 
Michael FranzCFOAuthor Commented:
Thanks Jim, but the field selector up front is a drop down list maintained by me, so no other choices......
0
 
Michael FranzCFOAuthor Commented:
AWESOME....Hope I allocated appropriately.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Close enough.  Thanks for the split.  Good luck with your project.  -Jim
0
 
awking00Commented:
Just add count(*) as 'Total_quoted'
0
All Courses

From novice to tech pro — start learning today.