473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Multiple counts(*) returning 1 record

2
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
Apr 2 '07 #1
4 2634
iburyak
1,017 Expert 512MB
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]
Apr 2 '07 #2
pafke
2
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]
Apr 2 '07 #3
Motoma
3,237 Expert 2GB
You can use boolean comparison operators as integers and then sum them.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     CustomerID,
  3.     SUM(status = 1) AS status1,
  4.     SUM(status = 2) AS status2,
  5.     SUM(status = 3) AS status3
  6. FROM
  7.     table
  8. GROUP BY
  9.     CustomerID
  10.  
Apr 2 '07 #4
iburyak
1,017 Expert 512MB
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.
Apr 2 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

66
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()
6
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...
4
by: randy.p.ho | last post by:
Using JDBC, is there a way to call a stored procedure with multiple return values? Thanks.
0
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...
2
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...
1
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...
52
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...
1
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...
1
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.