Need single row in sql query despite row count is 0

Question

Below is my query which returns no row and this is correct as per database records.

SELECT  CustomerID ,
        'Forwarder' AS CustType ,
        RForLocation ,
        YEAR(ReceivedDate) AS CurrentYear ,
        CONVERT(VARCHAR, COUNT(CASE WHEN MONTH(ReceivedDate) = 1 THEN 1
                                    ELSE 0
                               END)) AS Jan
FROM    RootTable
WHERE   Customerid = 12742
        AND YEAR(ReceivedDate) = 2014
GROUP BY CustomerID ,
        RForLocation ,
        YEAR(ReceivedDate)

But my requirement is that I should get a blank row with customerId, CustType,CurrentYear and Jan Count as Zero (0)

Below is my requirement

CustomerId  CustType   CurrentYear   Jan
12742       Forwarder   2014          0

Thanks Please Help

It is not clear. Which customer_id, custType, CurrentYear and Jan do you want to get, if there is no data in the table? — Guneli, May 05 '14 at 07:35
There is data for this customer in the table but they are not belong sto Year 2014 they all are belong to 2013. But my need is it should return row with zero count for Jan — Jim, May 05 '14 at 07:38
http://stackoverflow.com/questions/283120/assign-default-value-to-a-datacolumn-in-a-select-statement This may help... — Syed Mauze Rehan, May 05 '14 at 07:47

score 1 · Answer 1 · answered May 05 '14 at 07:42

1

SELECT    CustomerID 
        ,'Forwarder' AS CustType
        ,RForLocation 
        ,YEAR(ReceivedDate) AS CurrentYear
        ,ISNULL(
          NULLIF(
              COUNT(CASE WHEN MONTH(ReceivedDate) = 1 THEN 1 ELSE 0 END)
                 , 0)
              , '')
                                     AS Jan
FROM    RootTable
WHERE   Customerid = 12742
        AND YEAR(ReceivedDate) = 2014
GROUP BY CustomerID ,
        RForLocation ,
        YEAR(ReceivedDate)

answered May 05 '14 at 07:42

M.Ali

67,945
13
101
127

Not sure what do you mean by blank row. Show some sample data and desired output maybe then I can help. – M.Ali May 05 '14 at 08:28
only column headings.. CustomerId CustType CurrentYear Jan – Jim May 05 '14 at 09:02

G one · Answer 2 · 2014-05-05T07:54:16.260

0

Change your CASE statement

CONVERT(VARCHAR, CASE WHEN count(MONTH(ReceivedDate) = 1) 
                           THEN count(MONTH(ReceivedDate) = 1) 

               ELSE 0
end

edited May 05 '14 at 07:54

answered May 05 '14 at 07:44

G one

2,679
2
14
18

No, this wont work same result no rows and I need ReceivedDate=1 because later I will include Feb, Mar....all months of year. – Jim May 05 '14 at 07:49

Tomás · Answer 3 · 2014-05-05T10:44:44.383

0

SELECT  
RootTable.CustomerID ,
'Forwarder' AS CustType ,
RForLocation ,
YEAR(ReceivedDate) AS CurrentYear,
SUB1.MON_COL,
CASE WHEN sub1.CNT IS NULL THEN 0 ELSE sub1.CNT END AS CNT    
FROM    RootTable
LEFT JOIN 
(
    SELECT COUNT(1) CNT, CustomerId,MONTH(ReceivedDate) AS MON_COL,YEAR(ReceivedDate) AS YEAR_COL
    FROM    RootTable
    GROUP BY CustomerId,MONTH(ReceivedDate),YEAR(ReceivedDate)
) sub1 ON sub1.CustomerID = RootTable.CustomerId AND MONTH(ReceivedDate) = sub1.MON_COL AND YEAR(ReceivedDate) = sub1.YEAR_COL

WHERE RootTable.CustomerId=2

edited May 05 '14 at 10:44

answered May 05 '14 at 09:23

Tomás

535
6
23

Please Read my post again...Instead of now row I need a row with zero value of Jan or any month.. – Jim May 05 '14 at 09:43
If there is no record in the database is there any way to get a blank row with these values... – Jim May 05 '14 at 09:59
CustomerId CustType CurrentYear Jan 12742 Forwarder 2014 0 – Jim May 05 '14 at 09:59
I have editted my answer. Can you re-try the code. Unfortunately, I don't have access to MySQL instance to test against. – Tomás May 05 '14 at 10:09
this query is required in SQL Server. Your query is showing error on this line CASE WHEN sub1.CNT IS NULL THEN 0 ELSE sub.CNT AS MONTH_NAME, – Jim May 05 '14 at 10:14
I think I got my wires crossed with this and another SQL question. Sorry bout that. I have editted my answer. It is tested and seems to work. If, however there are no records with a received date of 2014 then it will return no rows either. If you still want it to return rows in this case then it will require another edit. – Tomás May 05 '14 at 10:30
If, there are no records with a received date of 2014 then it should return a row...this is the actual requirement – Jim May 05 '14 at 10:36

score 0 · Accepted Answer · answered May 05 '14 at 10:10

Whatever I could understand, please find the script and the updated query, with which, I tried it.

Created the table:

Create table RootTable
(CustomerID int, 
    RForLocation varchar(20), 
    ReceivedDate datetime)

Inserted the data:

insert into RootTable
Values(1, 'A', '1-Jan-2013'),
    (2, 'A', '1-Feb-2013'),
    (3, 'B', '1-Jan-2013'),
    (4, 'B', '1-Mar-2013')

Query to fetch the records :

SELECT  CustomerID ,
        'Forwarder' AS CustType ,
        RForLocation ,
        '2014' AS CurrentYear ,
        CONVERT(VARCHAR, COUNT(CASE WHEN MONTH(ReceivedDate) = 1 AND YEAR(ReceivedDate) = 2014 THEN 1
                                    ELSE null
                               END)) AS Jan
FROM    RootTable
WHERE   Customerid = 1
GROUP BY CustomerID ,
        RForLocation;

Since I want record for customer even if he does not have any record for specific year, I don't need to give condition of date in where clause, instead I can do the counting based on year, which will give me required output.

Hope it helps.

Need single row in sql query despite row count is 0

4 Answers4