CODEWITHSUNDEEP
sql-server-2008-r2
Francis
Francis

Reputation: 27

Select column value dependant on the value of another column

I need to produce a report and replace all NULL values in a 'NUMBER' column with a value between 1 and 10 dependant on the value of another 'VALUE' column and then total them up. Some of 'NUMBER' have been included already, but some have not. Basically, this is what I have -

**NUMBER**   **VALUE**    **DATE**
NULL         Apple        2012-08-01
NULL         Pear         2012-08-01
NULL         Banana       
NULL         Apple        2012-08-02
1            Apple        
2            Pear         2012-08-04
3            Banana       2012-08-04

What I need to produce is a report that shows something like this -

**NUMBER**    **VALUE**    **TOTAL**    **DATE**
1             Apple        3            2
2             Pear         2            2
3             Banana       2            1

The problem I have is that there is another COUNT within the SQL and I can't work out a way getting everthing to work together. The current SQL is looks like this -

SELECT    NUMBER,
          VALUE,
          COUNT (NUMBER) AS TOTAL,
          COUNT (DATE) AS DATE

FROM      MY_TABLE

GROUP BY  NUMBER,
          VALUE

Thanks.

Upvotes: 0

Views: 584

Answers (1)

Adam Wenger
Adam Wenger

Reputation: 17540

If I understood your question correctly, this query will return the totals you desire:

WITH ValueCount AS
(
   SELECT mt.Value, COUNT(1) AS Total
   FROM #My_Table AS mt
   GROUP BY mt.Value
)
, DateCount AS
(
   SELECT mt.Value, COUNT(1) AS DateTotal
   FROM #My_Table AS mt
   WHERE mt.MyDate IS NOT NULL
   GROUP BY mt.Value
)
SELECT mt.Number, vc.Value, vc.Total, dc.DateTotal
FROM ValueCount AS vc
INNER JOIN #My_Table AS mt ON vc.Value = mt.Value
INNER JOIN DateCount AS dc ON vc.Value = dc.Value
WHERE mt.Number IS NOT NULL
ORDER BY mt.Number ASC;

I broke up totaling the Value from the Date because you were looking for different criteria. If you do not like the look of the Common Table Expression (CTE), you can accomplish this with multiple queries, or sub-queries as well.

When using a CTE, the preceeding query must end with a semi-colon.

Upvotes: 1

Related Questions