SQL CASE Output COUNT Into Two Columns

Aug 27, 2007

Hi Everyone,

 I am trying to do a query where I need to use as little C# as possible to build my gridview.  Basically I have a column called statusID.  There are about 15 options for this column but I only want to count certain ones.  I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed".  I also want it to count based on the month.  To accomplish this I have used CASE and GROUP BY.  This has worked to some extent.  Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January.  Unfortunately the query returns 2 records for the month.  The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value.  I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data.  My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL.  The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month.  Below is my query, any suggestions about how to approach this will be greatly appreciated.  If any clarification is needed please let me know.  Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable.

SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID

    WHEN 3 THEN COUNT(Attend.statusID)

    END AS fullUnitsUsed,CASE Attend.statusID

    WHEN 4 THEN COUNT(Attend.statusID)

    WHEN 14 THEN COUNT(Attend.statusID)

    WHEN 15 THEN COUNT(Attend.statusID)

    WHEN 16 THEN COUNT(Attend.statusID)

    WHEN 17 THEN COUNT(Attend.statusID)

    WHEN 18 THEN COUNT(Attend.statusID)

    WHEN 19 THEN COUNT(Attend.statusID)

    WHEN 20 THEN COUNT(Attend.statusID)

END AS halfUnitsUsed

FROM Attend INNER JOIN People ON Attend.personID = People.personID

                     INNER JOIN Property ON Attend.propertyID = Property.propertyID

                     INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID

                    CROSS JOIN OpenDays

WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007')

GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID

ORDER BY Property.Name, fullName, NYSDDSORegion.Description

View 2 Replies


ADVERTISEMENT

Using CASE WHEN With COUNT

Mar 13, 2008

I need to check and see if there are multiple dates here and if there aren't then return the date, but if there are return the word various.

CASE WHEN COUNT(vew_DSG_BlackBookDetail.dDistribution) = 1 THEN vew_DSG_BlackBookDetail.dDistribution ELSE 'Various' END

I tried it like this, but get back the following error:

Didn't expect COUNT after the SELECT column list.

Any help would be greatly appreciated.

Thank you!

View 13 Replies View Related

Count In Case Statement

Apr 24, 2014

I have to count the number of Ideas and Markets here.

CASE WHEN Team IN ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') THEN 'Ideas' ELSE 'Markets' END

View 4 Replies View Related

Trying To Count A Case Statement?

Dec 3, 2007

I need to get a total count of leads and then separate the counts by either Retail or Wholesale -


Here's my table schema -

CREATE TABLE [dbo].[Sent] (
[IdentID] [int] IDENTITY (1, 1) NOT NULL ,
[LeadID] [bigint] NOT NULL ,
[AffiliateID] [bigint] NULL ,
[PartnerID] [int] NULL ,
[FranchiseID] [bigint] NULL ,
[FirstName] [t_Name] NULL ,
[LastName] [t_LastName] NULL ,
[Address] [t_Address] NULL ,
[Zip] [t_ZipCode] NULL ,
[Make] [t_Make] NULL ,
[Model] [t_Model] NULL ,
[DateIn] [datetime] NULL ,

Here's my query - Since I'm grouping by the partnerid


select distinct make, count(leadid) as TotalCount, case
when PartnerID = 1 then 'retail'
else 'wholesale' end
as disposition
from leads_sent (nolock)where datein between '2007-09-01' and '2007-09-30'
group by make, partnerid
order by make

Here's a sample my current output -

Acura
1 wholesale Acura
2 wholesale Acura
4 wholesale Acura
5 wholesale Acura
21 wholesale Acura
34 wholesale Acura
37 wholesale Acura
56 wholesale Acura
57 wholesale Acura
72 wholesale Acura
510 retail Audi
1 wholesale Audi
3 wholesale Audi
7 wholesale Audi
12 wholesale Audi
16 wholesale Audi
18 wholesale Audi
23 wholesale


Here's the output I need









Make
Total Count
RetailCount
WSCount

Acura
798
510
288

Audi
256
75
181

View 4 Replies View Related

External Columns Vs. Output Columns?

May 23, 2006

Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?

TIA,



Barkingdong

View 5 Replies View Related

Distinct Count, But Rewritten With Sum(case..)

Mar 28, 2007

It doesn't seem possibly, but maybe?
Is there a way to have an expression be used, but also benefit from using distinct on a column?

I'm looking for something like:
sum(case when dtEntered > '1-1-2006' then 1 else 0 end)
but also encorporating somehow a distinct count on UserName. So a username showing twice would only count once, and this would only be counted if the record's dtEntered date was greater than Jan 1, 2006.

The reason I'm writing the statement that way is because there are 5 columns which aggregate data by different time periods.

If it's not possible, I will just end up joining to the table multiple times, putting the date filter in the where clause.

View 3 Replies View Related

Help With Query (count With Case Statement)

Mar 29, 2008

Hi,

I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in
tblMessage

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")


any help much appreciated..

thanks!
mike123

View 7 Replies View Related

SQL Server 2012 :: Count Value In Case Statement

Aug 5, 2014

My table structure like below.

id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes

Want to calculate yes count on any day for same id

View 5 Replies View Related

SQL Statement, Adding Two COUNT/CASE Statements

Dec 12, 2007



SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors],
COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())

=======================

How do I add up both COUNT/CASE columns? Would it be:
SUM([New Visitors] + [Returning Visitors]) AS Total


I tried this and it doesn't work. I get invalid column names error for both.

I have even tried:
SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total

You would think that there would be some gui functionality in VS08 that would do this...


Thoughts are greatly appreciated!

TT

View 8 Replies View Related

Transact SQL :: Unable To Get Required Output Using Case Statement

May 17, 2015

Table Structure
EID    COLA    COLB
1    name    A
1    age    23
1    city    hyd
1    email    abc@live.in
1    mobile    45126
2    name    B
2    age    43

[code]....

how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null

View 9 Replies View Related

SQL 2012 :: Using Count Function And Case In One Select Statement

Jul 9, 2014

I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

Right now I am achieving this by the following case statement

SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

But can I do this with just one select statement?

View 2 Replies View Related

Checking Existence (repeating) And Output Count??

Nov 14, 2007

Hi All,

Not played with SQL for a while and am a bit rusty so please excuse me if I sound like a demented idiot! )

It's for a data migration from something wierd to SQL, I just need to be able to advise whether a table is worth migrating or someone should manually enter the data (i.e. if only six random fields are populated in a table then get a secretary to enter it).

I was hoping someone might have already done this and have a query I can edit?!

As far as I can make out the query needs to loop through every table, loop through every row, check each field for an existance of data and output something useful.

I've stared at this for about 3 hours now and tried several different things and none work.....and it's doing my nut! (

Help!!

Regs

View 2 Replies View Related

SQL Server 2014 :: CASE Condition Not Affecting CAST Output

Jun 10, 2014

I've a variable in my SP that I'd like to use to change the format of my output. Try this!

select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Then this:

select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):

select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2

Work fine.

View 8 Replies View Related

OUTPUT @count =no Of Records Returnd By Dynamic Where Clause

Aug 18, 2005

Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.

How should the query be constructed, executed, and then the Count(*) value returned?

"WHERE <condition1> AND <condition 2> ;


"AND <condition 2> " may exist or may not exist in the query; it is dependant."

View 1 Replies View Related

Trying To Set Output Variable To Row Count Result Of SQL Execute Task

Nov 5, 2007

I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!

SELECT count(*) FROM hsi.itemdata a
JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")

View 6 Replies View Related

Case Statement - Count Orders That Fall Into Specific Dollar Buckets

Aug 2, 2013

I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!

For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:

Code:
CASE
WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]

Is there an easier way to do this, maybe with a loop?

View 11 Replies View Related

Loop Query To Update Output Depending Upon Count Of Distinct Entries

Jun 25, 2013

I need to update the result depending upon the count of distinct entries.

Example

ID Employee Region State
1 ABC AMEA MI
2 DEF AMEA MI
3 XYZ APAC TX

I want the result as below

ID Employee Region State
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX

since the count of Region is 2

I tried using DECLARE @intFlag INT and stuff but wasn't able to get the solution.

View 2 Replies View Related

Getting Output Of A One Row Into 4 Columns

May 12, 2005

I am trying to find a way how to query following data from a table that looks like this:
ColA   ColB   ColC
1            2         3
Output should look like this:
ColA
1
2
3

View 2 Replies View Related

Count With 2 Columns?

Apr 9, 2006

im trying to display a category name in one column and then the number of books in that category in another column.

SELECT CATEGORY, COUNT(DISTINCT CATEGORY) "Category Total" FROM BOOKS;
Gives me an error saying something about category not being a single group function. What am I doing wrong?

View 4 Replies View Related

Count Of Columns &<&> 0

Jul 20, 2005

How would you count the number of columns with a value not equal to 0 foreach row in a table?Thanks!Joe

View 4 Replies View Related

ORDER BY, CASE, With Multiple Columns

Nov 7, 2003

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks


SELECT ....
ORDER BY (CASE Lower(@SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

View 5 Replies View Related

How To Make Columns Case Sensitive

Jun 14, 2001

Please help me, i want to know how to make sql server columns case sensitive for a login table.

View 1 Replies View Related

Rename All Columns To Upper Case

Oct 17, 2007

Hi,

I have a problem. I need to rename all columns of a database to uppercase. Since SQL SERVER 2005 does not support changing system tables is there a smooth way to do this? Has anyone ideas for a script? point me to the right direction.
I have found the stored procedure sp_rename which could be useful (or would it be better to alter the tables)...
So any help would be appriciated very much...

Regards

fb

View 4 Replies View Related

Can I Update Several Columns By CASE Expressions?

Apr 28, 2006

Hi, all here,

I am having a question-is it possible to update several columns using CASE expression in SQL language? like if I wanna set each CASE for each column.

Thanks a lot in advance for any help and guidance.

View 3 Replies View Related

Why Can't I Use Columns Based On Case Down In My Where Clause

Sep 13, 2007



I am writing a fairly simple sql, and I would like to write something like




Code Snippet
select

firstname as firstname,
case

when firstname = 'Peter' then 'yes'
else

'no'
end as whatever

from

MyTable
where

whatever = 'yes'



And this should then select out the rows where column number 2 is 'yes'.

It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.

But why?


View 4 Replies View Related

Transact SQL :: Case Statement With Different Columns

Oct 20, 2015

Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided.  The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access.  So I need to divide them out, and put them in one column for AccessLevel. 

 JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.

Case JobCode     (they all have same job code, but everyone in this job code should not have same access)
When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors)
When '25' Then '2' (this could be teacher, etc. )      
Case JobTitle   (this is how access should be)
        When '12345' Then '1' (this is Administration only) 

[code]....

View 12 Replies View Related

Re-sizing Output Columns

Apr 2, 2002

I've got a question that I can't seem to find an answer for, I was hoping someone here might be able to point me in the right direction. I've set up a stored procedure that will email someone if any entries are added to a table . However, the output is garbled looking (see below)

Client Number SSN Client Name Old SD
New SD
------------- ----------- ---------------------------------------- --------
--------
800901 899-34-3482 John Smith 04/20/20
05/01/20
400909 144-23-0029 John Smith 04/09/20
04/11/20
447788 445-89-9967 kjl;j;j 04/05/20
04/12/20
300099 234-90-7815 John Johnson 04/08/20
04/15/20

What's happened is the client name field is too wide, so the New SD field kicks down to the next line. I'd like to clean this up. Is there a way I can either increase the length of the row before it moves to the next line, or can I re-size the client name field to match the size of the data. In other words, cli_name_vc is declared as a varchar(40). If the longest name that comes up in the query is 18 characters long, can I re-size the output so that it does not take up 40 characters?

Any help is greatly appreciated.

thanks,
-scott

View 1 Replies View Related

How To Put Select Output Into Different Columns?

Oct 27, 2004

I have a table like:

ID Disc
----------------
1 BUSH
2 JOHN
1 GOLE
2 MIKE

I would like output depending on ID to put Disc into two columns. Like:

ID Disc1 Disc2
----------------------------
1 BUSH NULL
2 NULL JOHN
1 GOLE NULL
2 NULL MIKE

Any help will be appreciated. Thanks

ZYT

View 2 Replies View Related

Sum Columns Then Limit Output

Apr 24, 2012

I am trying to add two columns together and find all the entries that equal zero I have:

select table1.nbr, table2.itemvalue as CC
from table1, table2
where table1.nbr=table2.nbr and exists (select (OpnAc1+OpnAc2) as Open from table2

I want to take the sum of OpnAc1 and OpnAc2 and find all that equal 0, (meaning no open 1 or open 2)

View 2 Replies View Related

Combining Columns In Output

Feb 23, 2006

Using MySQL 5, MySQLQuery (latest). Complete Newbie,

I have the following query:

SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;

It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.

I think the query above is pretty self explanatory!

Many thanks.

View 4 Replies View Related

Subtract To Count Columns?

Sep 8, 2013

select Doctor, count(monthyear)- count(paidinfull) as Patients
from tableA
where paidinfull = 'YES'
group by doctor

So I have a table like

Doctors Patients
------ --------
Burns 12
White 7

where Doctors is a group by from tableA and Patients is a result of the row count of paidinfull = yes minus the rowcount of monthyear.

View 5 Replies View Related

Count The Columns In A Spreadsheet

Feb 21, 2008



Does anyone know how to get a count of columns in a spreadsheet before importing ?

Thanks in advance

View 8 Replies View Related

Using SQL Query Columns In Select Case Statements

Jun 5, 2006

I am using Visual Web Developer Express 2005 as a test environment.  I have it connected to a SQL 2000 server.  I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger.  Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
      Case 1
         execute an SQL Update Command
     Case 2
         execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like?  How would I access the column name used in the SQLDataSource?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved