Multiple sub queries

Question

Is it possible to get the result as below from the same table date-wise records:

               Enrolled   Enrolled as Email  Enrolled as Text Deals Redeemed   
<First Date>   7          5                  2                6
<Next Date>    9          3                  6               14

Table structure look something like this:

Customer_id, field1, field2, responsecode, created_date

My current query is something like this:

Select 
   Created,
   Enroll = (Select COUNT(*) from tblCustomer where field1 <> '' group by created),
   Email = (Select COUNT(field1) from tblCustomer where field1 = 'E-mail' and field1     <>    '' group by created),
   Cell = (Select COUNT(*) from tblCustomer where field1 = 'Cell Phone' and field1 <> ''    group by created)
from tblCustomer 
group by created 
order by created

What does the table look like? How is anyone supposed to answer this without the relevant information? — Klaus Byskov Pedersen, Feb 29 '12 at 10:31
please elaborate. do you mean the records which contain dates? or something else? — Sergey Benner, Feb 29 '12 at 10:32
Common field are as below: Customer_id,email,text,responsecode,created_date — Vishal Suthar, Feb 29 '12 at 10:33

score 3 · Answer 1 · answered Feb 29 '12 at 12:13

You don't want a COUNT(), but instead, a SUM( CASE/WHEN )

Select 
      Created, 
      count(*) TotalCnt,
      SUM( CASE WHEN Field1 = 'E-mail' then 1 else 0 END ) as EMailCnt,
      SUM( CASE WHEN Field1 = 'Cell Phone' then 1 else 0 END ) as CellCnt,
      SUM( CASE WHEN RedeamedCondition then 1 else 0 END ) as RedeamCnt
   from 
      tblCustomer  
   group by 
      created  
   order by 
      created

Note... if created is a date/time you will need to have the group by based on the date portion ONLY of the "created", otherwise you would get different counts for every second... From another post, the following gets only the date portion of a date time by basically removing the hours:minutes:seconds portion

DATEADD(dd, 0, DATEDIFF(dd, 0, created))

or if that doesn't make sense, you could just do it by

datepart( yy, created) as GrpYear,
datepart( mm, created) as GrpMonth,
datepart( dd, created) as GrpDay,  ... rest of columns.....

score 2 · Accepted Answer · 2012-03-01T07:21:17.913

2

Try:

select created_date,
       count(field1) Enrolled,
       count(case field1 when 'E-mail' then 1 end) Enrolled_as_Email,
       count(case field1 when 'Cell Phone' then 1 end) Enrolled_as_Cell,
       (Select COUNT(*)
        from tbl_TransactionDishout d
        where d.created = c.created_date and
              d.DishoutResponseCode = '0000') Deals_Redeemed
from tblCustomer c
group by created_date
order by created_date

edited Mar 01 '12 at 07:21

answered Feb 29 '12 at 10:40

@VishalSuthar: How about now? – Feb 29 '12 at 11:13
Please check my question..I have updated it by adding my current query which is not working. – Vishal Suthar Feb 29 '12 at 11:17
@VishalSuthar: Your table structure does not match the code in the queries - there is no `field1` in your table structure. Please can you correct your question? – Feb 29 '12 at 11:25
I have modified @Mark Bannister – Vishal Suthar Feb 29 '12 at 11:37
@VishalSuthar: I have again updated my answer - if this still does not answer your question then you must include more information: in what way does the amended query fail to meet your requirements? How is Enrolled as Text recorded on the table? How are Deals Redeemed stored on the table? Why are you storing customers with NULLs in field1? What is the purpose of field2? Are there any other relevant fields that have not been mentioned? – Feb 29 '12 at 11:57
Your query works fine till now but one column is wrong, for more information for that I added this: For Deals_Redeemed,I need another subquery Table name is "tbl_TransactionDishout" [Trnx_ID] [int] IDENTITY(1,1) NOT NULL, [OfferNo] [nvarchar](50) NULL, [MerchantID] [nvarchar](50) NULL, [TerminalID] [nvarchar](50) NULL, [DishoutResponseCode] [nvarchar](50) NULL, [Created] [datetime] NULL So the existing query for that is as below: Select COUNT(*) from tbl_TransactionDishout where DishoutResponseCode = '0000' So can you add this in your existing query.? – Vishal Suthar Mar 01 '12 at 06:05
Displays as this: 2010-05-20 00:00:00.000 1 1 0 133 2010-05-22 00:00:00.000 2 2 0 133 2010-05-25 00:00:00.000 5 4 1 133 2010-05-27 00:00:00.000 1 1 0 133 Which means same count in all rows – Vishal Suthar Mar 01 '12 at 07:19
@VishalSuthar: Updated accordingly - try now. – Mar 01 '12 at 07:22

Multiple sub queries

2 Answers2

Linked

Related