SQL case two conditions two columns

Question

I have the data as follow:

"Table" computer data

date       |    property   |    computer code
----------      --------        -----------
20160131   |   companyA   |     256584
20160131   |   companyB   |     987451

to e.t.c

20171020   |   companyA   |     157489

I want to count the number of computer in each company after 2017-Sep, so I use:

select
 computer data

 , count (case computer code when property='companyA' and date='20171001' then 1 else 0 end) as CMAnumber
 , count (case computer code when property='companyB' and date='20171001' then 1 else 0 end) as CMBnumber

from
[data]

group by
computer data

order by
computer data

but it not work...
please help!!

I use SQL server 2014 Management Studio. and the data base from company system

and I want to show it like

date |  CMAnumber | CMBnumber

20171001 |  200   |  210

20171002 |  230   |  207

ETC

sorry about that I am a fresh on SQL T.T

Care to elaborate about "but it not work"? Are you getting an error? The wrong results? — Mureinik, Oct 23 '17 at 06:41
`COUNT()` counts all non-null values, i.e. both 1 and 0 are counted. Do `SUM()` instead. — jarlh, Oct 23 '17 at 06:47
or use COUNT() to count, just feed it NULLS when that is needed — Paul Maxwell, Oct 23 '17 at 06:49
Are you getting an error or incorrect results? If error: ***What is the error message?*** If incorrect results: The query in your question should be producing an error, so you ***have given the wrong query***. (And don't expect useful help if you provide the ***wrong*** information.) — Disillusioned, Oct 23 '17 at 06:57
I got Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '=' — Billy Cheng, Oct 23 '17 at 07:07
and I don't know the code is correct or not for the performance — Billy Cheng, Oct 23 '17 at 07:07

score 2 · Answer 1 · answered Oct 23 '17 at 06:46

You are mixing two case forms.

The serached form has a condition after the when:

CASE WHEN <condition> ..

The simple form has it split: a common operand between case and when and expressions after the when. It implies an equals comparision:

CASE <common operand> WHEN <expression> ...

More about case: http://modern-sql.com/feature/case

score 1 · Accepted Answer · edited Oct 23 '17 at 08:08

1

If you want to use CASE for count some conditions, and if you choose COUNT function for it, else part of the case should be NULL, otherwise, it counts else condition too. Or you should use SUM instead of COUNT. I updated the script for COUNT function.

And your case using wrong, I corrected it too.

SELECT
 [computer data]
 , count (case when property='companyA' and date >= '20171001' then 1 end) as CMAnumber
 , count (case when property='companyB' and date >= '20171001' then 1 end) as CMBnumber
from
    [data]
where
    date >= '20171001'
group by
    [computer data]
order by
    [computer data]

edited Oct 23 '17 at 08:08

Billy Cheng

15
5

answered Oct 23 '17 at 06:54

Serkan Arslan

13,158
4
29
44

the date can't be sort after Oct-2017, the data shown from Jan-2011 and all the number be 0 – Billy Cheng Oct 23 '17 at 07:05
and I have one more question, all the date is shown on the table – Billy Cheng Oct 23 '17 at 08:02
like 20110101 is shown with 0 and 0. unit 20171001 is shown 200 210 (correct data). how can I sort the date only show 20171001 for the start – Billy Cheng Oct 23 '17 at 08:04
If you have the date column in the select part of the query, you can add it to order by too. – Serkan Arslan Oct 23 '17 at 08:11

score 0 · Answer 3 · answered Oct 23 '17 at 06:44

0

What about this:

SELECT property , COUNT(*)
  FROM [data]
 WHERE DATE > '20170930'
GROUP BY property ;

answered Oct 23 '17 at 06:44

FDavidov

3,505
6
23
59

@CraigYoung, I did and, if the PO is interested only in a collection of companies (like he wrote), he can simply add a `AND property IN (...)`. I see no reason to make things complex when they can be very simple. The whole `case` construct is not needed at all. The only this is a PIVOT of the answer, which could be arranged by a simple SELECT wrapping the one I suggested. – FDavidov Oct 23 '17 at 09:10
OP is quite clearly trying to get 2 separate counts for 2 different companies. Your answer comes nowhere near doing that. Your answer also doesn't do anything to address either of the 2 specific problems OP is likely facing. – Disillusioned Oct 23 '17 at 13:07
@CraigYoung, SO is aimed to either give a working solution or suggest a different way of thinking. What the OP needs can be quite easily achieved by adding a second `GROUP BY` (date) and pivoting the result to get the counts. In my mind, I consider the situation in which he does not need only two companies but a variable number of companies, in which case the presented SELECT (with the case) in the question and the marked answer would become quite ... **a nightmare(!)**. Having said that, your point is clear as it is mine. Let's agree that we disagree. Deal? – FDavidov Oct 25 '17 at 11:39

Kishore Kumar · Answer 4 · 2017-10-23T08:13:47.683

0

You can do a combination of subQuery and group by

select property,count(*) from (select * from [computer data] where date > '20170930') group by property

edited Oct 23 '17 at 08:13

answered Oct 23 '17 at 06:58

Kishore Kumar

12,675
27
97
154

I got Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '='. – Billy Cheng Oct 23 '17 at 07:04
Check whether you table name and column names are correct – Kishore Kumar Oct 23 '17 at 08:14

SQL case two conditions two columns

4 Answers4