Imagine you have a table with the following fields:
Customer-id nvarchar(50)
status int
Records :
Customer id status
X 1
X 2
Y 1
Y 3
Z 2
X 1
I would like to have one row with the result from the counts(*) and grouped by.
Output:
Customer id status1 status2 status3
X 2 1 0
Y 1 0 1
Z 1 0 0
How is this done in MS sql
4 2634
Try this:
[PHP]declare @a table([Customer id] char(1), status int)
insert into @a values ('X', 1)
insert into @a values ('X', 2)
insert into @a values ('Y', 1)
insert into @a values ('Y', 3)
insert into @a values ('Z', 2)
insert into @a values ('X', 1)
select [Customer id],
sum(case when status = 1 then 1 else 0 end) status1,
sum(case when status = 2 then 1 else 0 end) status2,
sum(case when status = 3 then 1 else 0 end) status3
from @a
GROUP BY [Customer id][/PHP]
I don't need a sum but a count (*) number of rows per category.
Try this:
[PHP]declare @a table([Customer id] char(1), status int)
insert into @a values ('X', 1)
insert into @a values ('X', 2)
insert into @a values ('Y', 1)
insert into @a values ('Y', 3)
insert into @a values ('Z', 2)
insert into @a values ('X', 1)
select [Customer id],
sum(case when status = 1 then 1 else 0 end) status1,
sum(case when status = 2 then 1 else 0 end) status2,
sum(case when status = 3 then 1 else 0 end) status3
from @a
GROUP BY [Customer id][/PHP]
You can use boolean comparison operators as integers and then sum them. -
SELECT
-
CustomerID,
-
SUM(status = 1) AS status1,
-
SUM(status = 2) AS status2,
-
SUM(status = 3) AS status3
-
FROM
-
table
-
GROUP BY
-
CustomerID
-
In my query sum in this case works like a counter where I put 1 if condition is true and 0 if not. If I summarize True conditions it will work like a count.
Run a query and see the result.
Good Luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Darren Dale |
last post by:
Hello,
def test(data):
i = ? This is the line I have trouble with
if i==1: return data
else: return data
a,b,c,d = test()
|
by: Samuel Hon |
last post by:
Hi
I'm not sure what the best approach for this is:
I have a stored procedure which I would like to use to return several
output values instead of returning a recordset.
CREATE PROCEDURE...
|
by: randy.p.ho |
last post by:
Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.
|
by: Richard Holliingsworth |
last post by:
Hello:
I'm building a report to display statistics on the entire database. I
have successfully built a report that groups the entire db on one field
and gives me a count of db records for the...
|
by: DaveDiego |
last post by:
I'm building a report that has a count of cases for employees. I have
separate queries that count, OpenCases, NewCases, TotalCases, Etc. I would
like to put all those counts into a record for each...
|
by: NumberCruncher |
last post by:
Hi All,
I am struggling with setting up my first system of tables, forms,and reports, and could use your help!
I am setting up a database to keep track of the production of a produced item. The...
|
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible...
|
by: josecruz |
last post by:
I have to create a summary report that will provide counts and # average of days for multiple "Status" by entering different dates for every criteria. I have created several queries that calculates...
|
by: javediq143 |
last post by:
Hi All,
This is my first post in this forum. I'm developing a CMS for my latest website. This CMS is also in PhP & MySQL. I'm done with the ADD section where the Admin can INSERT new records in...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |