Wednesday, March 28, 2012
How to assign values to variables in a procedure in with select s
I have code like this
:
CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
OUTPUT,@.sex VARCHAR(1) OUTPUT
AS
SELECT NAME,SEX from table1 where .....
Now I want to assign the value of Name and sex from the select statement to
variables @.name and @.sex.
How do I do that. I just do not want to execute the select statement twice
as the selection criteria is big.
Thanks,
JSSELECT @.name = name, @.sex = sex
FROM table1
WHERE (something that guarantees exactly one row)
"JS" <JS@.discussions.microsoft.com> wrote in message
news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
> Hi,
> I have code like this
> :
> CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
> OUTPUT,@.sex VARCHAR(1) OUTPUT
> AS
> SELECT NAME,SEX from table1 where .....
> Now I want to assign the value of Name and sex from the select statement
> to
> variables @.name and @.sex.
> How do I do that. I just do not want to execute the select statement twice
> as the selection criteria is big.
> Thanks,
> JS
>sql
How to assign values to variables in a procedure in with sele
Error141 - A select statement that assigns a value to a variable must not be
combined with data retrieval operation.
I am using SQL Server 7 .
Any help will be greatly appreciated.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT @.name = name, @.sex = sex
> FROM table1
> WHERE (something that guarantees exactly one row)
>
> "JS" <JS@.discussions.microsoft.com> wrote in message
> news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
>
>> Error141 - A select statement that assigns a value to a variable must not
> be
> combined with data retrieval operation.
Then you didn't run exactly what I posted. Could you show exactly what you
tried to run?
You can't SELECT and assign in the same statement. So, if you are sure that
the WHERE clause will always limit to one row, *and* for some reason you
need to SELECT the data *and* return it in output parameters (why would you
need to do both?), you can do this:
-- assign variable values from table
SELECT @.name = name, @.sex = sex FROM table WHERE ...
-- return *variables* to client
SELECT name = @.name, sex = @.sex|||JS wrote:
> I tried that
I doubt that you tried exactly what he said. It would help if you showed us
the revised code, but I suspect your statement now looks like:
SELECT NAME,SEX,@.name = name, @.sex = sex
from table1 where .....
Right?
> but get an error for syntax check
> Error141 - A select statement that assigns a value to a variable must
> not be combined with data retrieval operation.
That's a pretty self-explanatory error message: in a single sql statement
you can either return data to the client or assign values to variables. You
cannot do both in a single statement.
I suspect what you are trying to to do is:
SELECT name = name, @.sex = sex
from table1 where .....
SELECT @.name, @.sex
> I am using SQL Server 7 .
>
Doesn't matter
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Here it is:
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
"Aaron Bertrand [SQL Server MVP]" wrote:
> Then you didn't run exactly what I posted. Could you show exactly what yo
u
> tried to run?
> You can't SELECT and assign in the same statement. So, if you are sure th
at
> the WHERE clause will always limit to one row, *and* for some reason you
> need to SELECT the data *and* return it in output parameters (why would yo
u
> need to do both?), you can do this:
> -- assign variable values from table
> SELECT @.name = name, @.sex = sex FROM table WHERE ...
> -- return *variables* to client
> SELECT name = @.name, sex = @.sex
>
>|||Here is my code
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
What I am trying to do is assign the values to the OUTPUT variables, so that
the client can see the values of the OUTPUT field.
Please help
"Bob Barrows [MVP]" wrote:
> JS wrote:
> I doubt that you tried exactly what he said. It would help if you showed u
s
> the revised code, but I suspect your statement now looks like:
> SELECT NAME,SEX,@.name = name, @.sex = sex
> from table1 where .....
> Right?
>
> That's a pretty self-explanatory error message: in a single sql statement
> you can either return data to the client or assign values to variables. Yo
u
> cannot do both in a single statement.
> I suspect what you are trying to to do is:
> SELECT name = name, @.sex = sex
> from table1 where .....
> SELECT @.name, @.sex
>
> Doesn't matter
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30)
> OUTPUT,@.sex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
(a) do you not have a primary key on SSN? If so, there is no need to set
rowcount 1, since there will only ever be a maximum of one match. If there
is no primary key, why not?
(b) sorry, but there is no way that the procedure above yields the error you
mentioned earlier. Either you transcribed it wrong or you are looking at
the wrong code.|||Also,
(a) can you come up with a more useless and generic name for your procedure
than GET_DATA? At least you use the dbo prefix...
(b) I recommend better formatting so your procedure is readable.
(c) I strongly recommend against these implicit, non-ANSI join syntaxes.
How about:
CREATE PROCEDURE dbo.GetNameSexData
@.ssn VARCHAR(9),
@.name VARCHAR(30) OUTPUT,
@.sex VARCHAR(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT 1
SELECT
@.name = nt.Name,
@.sex = ot.Sex
FROM
Name_Table nt
INNER JOIN Other_Table ot
ON nt.ssn = ot.ssn
WHERE nt.ssn = @.ssn
END
GO
Now, if that produces an error when you *call* it, show the method you used
to *call* it.|||Got it I was declaring the variables but assigining all the values in the
select statement for the output type. Thanks very much for all the help
"JS" wrote:
> Here is my code
> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.s
ex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
> What I am trying to do is assign the values to the OUTPUT variables, so th
at
> the client can see the values of the OUTPUT field.
> Please help
> "Bob Barrows [MVP]" wrote:
>
How to assign values to a variable from a xls sheet?
I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?
select Proyecto from [Carga$]
TIA,
I'm so sorry it's solved!!
I promise you that from now on I'll try not be so impatient..
How to assign OR get Time value to a colum
How can i store time value in a column. And also i need to compare the values. Another problem is , how can i get the Current time of the server.(something like getdate() ?)
Happy Coding
hi,
there's no other function aside form getdate().
if you want it to be stored automatically to a column you have to enforce a "default constraint"
thanks,
joey
|||MasterG wrote:
How can i store time value in a column. And also i need to compare the values. Another problem is , how can i get the Current time of the server.(something like getdate() ?)
Happy Coding
Hi,
Please see answer; http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1000324&SiteID=1
Most people wont like you double posting a question here, but it looks like your new so I guess everyone will let you off, just the once
Happy Coding!
hth
Pace|||
What a shame but sorry for duplicate post. But it is caused from my web browser. Sorry for that , and also thanks for your solution.
Very Happy Coding now ha .....
How to Assign Identity Key values in Replication
This is a very basic question on replication.
I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.
Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.
Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-
1. I don't want to use GUID.
2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.
i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.
any help will be highly appreciable.
You can specify identity ranges. See books online topic "Replicating Identity Columns". You can also search books online for "replication identity" for a range of topics.|||thanks for your reply.
i saw the topics which you have mentioned. As per my requirement i/o specify individual ranges i have to keep this Identity column in Sequence for all sites. so i will have to do this manually..
is it possible to point towards some code which does that as i'm sure this is a very common requirement and lot of people must have already written generic code to accomplish this.
Monday, March 26, 2012
How to append a new values like uniqueidentifiers, sequence numbers to data flow?
Hi,
I would like to know different possible ways in appending extra values like new uniqueidentifiers, sequence numbers, random number. Can you please tell what type of data flow components helps us ?
Append what to where? Do you want to add new columns with such vaues to a path within the data flow task?
Derived Column Transform?
Script Transform?
Row Number Transform ? (http://www.sqlis.com/default.aspx?77)
|||yes..i want to append values to a path with in the data flow task.
If i use Derived Column Transformation, how can i generate uniqueidentifiers and sequence numbers for each row?
Thanks
Gopi
|||For GUIDs you would need to use the script task, as there is no "New Guid" function in the expression syntax.
For sequence numbers or row numbers I assume, then again the script task is required. For an easier life you can use the cunningly named Row Number Transform I mentioned previously, which does just that, and comes as a simple MSI to install.
|||Darren,
Thank you very much.
sqlWednesday, March 21, 2012
How to align two data regions?
I have a bar chart control with a Y-Axis that has values 1, 2, 3, 4 and 5.
To the right of it, I have a table control that will always have five rows of
data (ea. row associated to the values on the Y-Axis of the chart control).
I would like to arrange the two controls on my report so that the 5 rows in
the table control ALIGN with the 5 values on the Y-Axis of the chart control.
Any recommendations? Thanks - GPlease check my response on your first thread on how to customize the y-axis
labels.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:9E4DDFF4-BCF2-4249-9EDF-D7F95B61CAF5@.microsoft.com...
> Here is the scenario ...
> I have a bar chart control with a Y-Axis that has values 1, 2, 3, 4 and 5.
> To the right of it, I have a table control that will always have five rows
> of
> data (ea. row associated to the values on the Y-Axis of the chart
> control).
> I would like to arrange the two controls on my report so that the 5 rows
> in
> the table control ALIGN with the 5 values on the Y-Axis of the chart
> control.
> Any recommendations? Thanks - Gsql
how to add the values in a column
try sum, ie select sum(OrderValue) from orders
You may have to group the orders though, here from northwind I get a sum for each customer
select customerId,sum(freight) from orders group by customerid
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"qqqqqqqqqqqqqqqqqqqqqqq" wrote:
> Display The total value of all the orders put together
>
>
Monday, March 12, 2012
How to add new values into a aspnet_membership?
First of all, I used createnewuserwizd in vwd05 and customize more inputbox than the default, like firstname, and lastname, now I would like to add those new values into a aspnet_membership table, I try to modified the aspnet_membership_createUser store procedure, but it doesn't work ...... Here is my aspnet_membership_createUser code:
------> Code Begin <------------
ALTER PROCEDURE
dbo.aspnet_Membership_CreateUser@.ApplicationName
nvarchar(256),@.UserName
nvarchar(256),@.Password
nvarchar(128),@.FirstName
nvarchar(128),@.PasswordSalt
nvarchar(128),@.PasswordQuestion
nvarchar(256),@.PasswordAnswer
nvarchar(128),@.IsApproved
bit,@.CurrentTimeUtc
datetime,@.CreateDate
datetime=NULL,@.UniqueEmail
int= 0,@.PasswordFormat
int= 0,@.UserId
uniqueidentifier OUTPUTAS
BEGIN
DECLARE@.ApplicationIduniqueidentifierSELECT@.ApplicationId =NULLDECLARE@.NewUserIduniqueidentifierSELECT@.NewUserId =NULLDECLARE@.IsLockedOutbitSET@.IsLockedOut = 0DECLARE@.LastLockoutDatedatetimeSET@.LastLockoutDate =CONVERT(datetime,'17540101', 112 )DECLARE@.FailedPasswordAttemptCountintSET@.FailedPasswordAttemptCount = 0DECLARE@.FailedPasswordAttemptWindowStartdatetimeSET@.FailedPasswordAttemptWindowStart =CONVERT(datetime,'17540101', 112 )DECLARE@.FailedPasswordAnswerAttemptCountintSET@.FailedPasswordAnswerAttemptCount = 0DECLARE@.FailedPasswordAnswerAttemptWindowStartdatetimeSET@.FailedPasswordAnswerAttemptWindowStart =CONVERT(datetime,'17540101', 112 )DECLARE@.NewUserCreatedbitDECLARE@.ReturnValueintSET@.ReturnValue = 0DECLARE@.ErrorCodeintSET@.ErrorCode = 0DECLARE@.TranStartedbitSET@.TranStarted = 0IF( @.@.TRANCOUNT = 0 )BEGINBEGIN TRANSACTIONSET@.TranStarted = 1ENDELSESET@.TranStarted = 0EXECdbo.aspnet_Applications_CreateApplication @.ApplicationName, @.ApplicationIdOUTPUTIF( @.@.ERROR <> 0 )BEGINSET@.ErrorCode = -1GOTOCleanupENDSET@.CreateDate = @.CurrentTimeUtcSELECT@.NewUserId = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserNameAND@.ApplicationId = ApplicationIdIF( @.NewUserIdIS NULL)BEGINSET@.NewUserId = @.UserIdEXEC@.ReturnValue = dbo.aspnet_Users_CreateUser @.ApplicationId, @.UserName, 0, @.CreateDate, @.NewUserIdOUTPUTSET@.NewUserCreated = 1ENDELSEBEGINSET@.NewUserCreated = 0IF( @.NewUserId <> @.UserIdAND@.UserIdIS NOT NULL)BEGINSET@.ErrorCode = 6GOTOCleanupENDENDIF( @.@.ERROR <> 0 )BEGINSET@.ErrorCode = -1GOTOCleanupENDIF( @.ReturnValue = -1 )BEGINSET@.ErrorCode = 10GOTOCleanupENDIF(EXISTS(SELECTUserIdFROMdbo.aspnet_MembershipWHERE@.NewUserId = UserId ) )BEGINSET@.ErrorCode = 6GOTOCleanupENDSET@.UserId = @.NewUserIdIF(@.UniqueEmail = 1)BEGINIF(EXISTS(SELECT*FROMdbo.aspnet_Membership mWITH(UPDLOCK,HOLDLOCK)WHEREApplicationId = @.ApplicationIdANDLoweredEmail =LOWER(@.Email)))BEGINSET@.ErrorCode = 7GOTOCleanupENDENDIF(@.NewUserCreated = 0)BEGINUPDATEdbo.aspnet_UsersSETLastActivityDate = @.CreateDateWHERE@.UserId = UserIdIF( @.@.ERROR <> 0 )BEGINSET@.ErrorCode = -1GOTOCleanupENDENDINSERT INTOdbo.aspnet_Membership( ApplicationId,
UserId,
Password,
FirstName,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES( @.ApplicationId,@.UserId,
@.Password,
@.FirstName,
@.PasswordSalt,
@.Email,
LOWER(@.Email),@.PasswordQuestion,
@.PasswordAnswer,
@.PasswordFormat,
@.IsApproved,
@.IsLockedOut,
@.CreateDate,
@.CreateDate,
@.CreateDate,
@.LastLockoutDate,
@.FailedPasswordAttemptCount,
@.FailedPasswordAttemptWindowStart,
@.FailedPasswordAnswerAttemptCount,
@.FailedPasswordAnswerAttemptWindowStart )
IF( @.@.ERROR <> 0 )BEGINSET@.ErrorCode = -1GOTOCleanupENDIF( @.TranStarted = 1 )BEGINSET@.TranStarted = 0COMMIT TRANSACTIONENDRETURN0Cleanup:
IF( @.TranStarted = 1 )BEGINSET@.TranStarted = 0ROLLBACK TRANSACTIONENDRETURN@.ErrorCodeEND
------------> Code End <----------------
Please Help me out I try everything and everything, but the right thing please help me out, I really appreciated ......
You alter the stored procedure to only add a firstname value? You haven't change the logic of the sp, right? Then what's the meaning of " it doesn't work "? No user can be added through the sp? Or any error message?|||Yeah, I tried that, too. I just posted in the FAQ about how I added a MobileId to the Comment column in the aspnet_membership. You can't do anything esle with it apparently, however you don't need to! You can add a new WizardStep and store the data on that step to a new table. i hope this helps! Check the FAQ under customized createuserwizard.|||I would have put those values in the users profile rather than membership. A user's last and first name really have nothing to do with membership. Then again, I like stuffing everything into profile.How to add Identity property to existing table?
modify the table structure.
Originally posted by randyfoy
Has anybody ever tried to do this. I can't figure it out. All I want to do is take an existing table that already has values in the column that I want to change and add the identity property to yes and set the identity seed and increment to a specific number. I know you can do it in the CREATE TABLE statement but is there a way to use the ALTER TABLE command?|||even though I can just go to the design view of the table through the Enterprise Manager and change the property? It works fine if I do it manually.|||and try to run profiler while you're doing it, guess you'd answer your own question
Friday, March 9, 2012
How to add Drill down option in the Dataset ( + )
Dear Friends,
I have created a Dataset and displayed in the report. In the report, its showing as expanded values by default. There is no option for me to Collapse and Expand.
If I create a Table with Report Wizard, I am getting the Collapse & Expand ( + ) option in the report.
Is there anyway, I can add the Collapse / Expand in the Dataset kind of reports.
Thanks.
Warm Regards
Rakin
Singapore.
If you investigate the report that is generated through the report wizard, you will notice that the toggle-effect is achieved by setting the Visibility property e.g. of table rows to be toggled by a certain other textbox.
Please also check RS BOL for more information how to add toggles in report designer: http://msdn2.microsoft.com/en-us/library/ms156456.aspx
-- Robert
|||Thanks Robert.
Now I am able to do that. It works fine.
Regards
Rakin.
Wednesday, March 7, 2012
How to add an "if then else" condition within an expression?
Greetings:
I am creating a complex expression in a Send Mail Task for the MessageSource. The expression contains the values of many package variables. I want to conditionally include some variables in the expression depending on what value they contain.
Is it possible to create conditional logic (if then else) within an expression? I've got to conditionally include 8 different variables.
Thanks,
BCB
Using the expression editor, you can look at the Operators. One of them is the "conditional."This is what you want. (?
your test ? if true : else false|||
Thanks, Phil.
This is part of the code I came up with to conditionally include "lines" in the body of my email message. The 2nd and 4th lines will appear only if the value of a variable is not empty. (Those are the lines that begin with Trim.) The key thing I discovered is that you need to enclose the conditional logic in parens in order for the overall expression to evaluate properly.
"The number of ESHL rows inserted was " + (DT_STR, 10, 1252) @.[User::ESHL_RowsInserted] + "." + "\n" +
(Trim(@.[User::ESHL_Error_LongMessage]) == "" ? "" : "\n" + "ESHL Message: " + @.[User::ESHL_Error_LongMessage] + "\n\n") +
"The number of Gulf States rows inserted was " + (DT_STR, 10, 1252) @.[User::GulfStates_RowsInserted] + "." + "\n" +
(Trim(@.[User::GulfStates_Error_LongMessage]) == "" ? "" : "\n" + "Gulf States Message: " + @.[User::GulfStates_Error_LongMessage] + "\n\n")
How to add all the values in a single column in a table?
numbers
2
4
2
Above is an example of my table with a single column. My problem is how to add all the numbers in that column to make it 8? The rows are also dynamic.
Your help is highly appreciated.
Is this what you are looking for:
SELECT SUM(numbers) AS total
Does this help?
|||select sum(numbers) from table|||
You need to use a SUM function
select sum(numbers) from yourTable
Nick
|||Thats exactly really how i want it to be...! Thanks for the great help everyone....I really appreciate it...!Friday, February 24, 2012
How to Add a column by getting value from a lookup table
I have few columns at some level, and now I need to add a column from another database table based on the values from one of the existing columns.
It seems that Lookup Transformation doesnt support parameterized query.
Any clue ?
Thanks
Fahad
Are you sure you need a aprameterized query?
in the the lookup trasnform you can write a query; the join the columns of the lookup table/query with the columns available in the data pipeline.
Keep in mind, that you could have an aditional source component and then combine them using a Merge Join transform.
|||I agree with Rafael... By joining columns to the lookup table/query, you're effectively using "parameters". Unless you need your lookup query to be dynamic for each record coming down through the data flow.|||Yes, I know, but still I need to pass some values to the stored procedure to get a huge list, and then I will select from the list.Am I using the wrong Transform Component ?|||Maybe. Have you looked at using the OLE DB Command transform?|||Yes, and it seems it doesnt return the cardinality return by query to its output pipeline.|||
Fahad,
I still don't see how a the lookup trasnform or the 2 SOurce components plus the Merge join cannot help you. Pherhaps if you provide an example you may be more likely to receive more suggestions.
How to add a calender with a user parameter
Can any one help me how to add a calender as a list of
values,to an user parameter in reports. I am passing a parameter
called effective date. for that parameter i need calender as a list of
value. Please any one help on this..
thanks
BalajiThis is built into RS 2005. Just go to layout view, Report Menu ->Report
Parameters and change the data type to datetime. You will now have a
calendar. If you are on RS 2000 you are out of luck.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rampaleee@.gmail.com> wrote in message
news:1193739093.504989.145020@.o38g2000hse.googlegroups.com...
> Hello evry one,
> Can any one help me how to add a calender as a list of
> values,to an user parameter in reports. I am passing a parameter
> called effective date. for that parameter i need calender as a list of
> value. Please any one help on this..
> thanks
> Balaji
>
how to accumulate values in different columns ?
I have a table similar to this :
id | name | res_id
--+---+---
1 | foo | 1
2 | bar | 1
3 | foo | 1
4 | test | 2
5 | wheyho | 3
6 | ding | 2
7 | dong | 3
8 | foo | 2
9 | foo | 3
10 | bar | 1
11 | bar | 2
12 | bar | 2
13 | wheyho | 3
Now, I am looking for a SELECT statement that counts the result_id depending on its value ( 1,2 or 3 in this example ) and grouped by name. To make it a bit more clear I am looking for this result (with result_id 1 evaluates to 'pass', 2 to 'fail' and 3 to 'unknown' ):
name | pass | fail | unknown
---+--+--+----
foo | 2 | 1 | 1
bar | 2 | 2 | 0
test | 0 | 1 | 0
wheyho | 0 | 0 | 2
ding | 0 | 1 | 0
dong | 0 | 0 | 1
I can get single results when doing this staements :
SELECT name, count(result_id) AS pass FROM t1 GROUP BY name, res_id HAVING res_id = 1;
name | pass
--+--
foo | 2
bar | 2
SELECT name, count(result_id) AS fail FROM t1 GROUP BY name, res_id HAVING res_id = 2;
name | fail
--+--
foo | 1
bar | 2
ding | 1
test | 1
SELECT name, count(result_id) AS unknown FROM t1 GROUP BY name, res_id HAVING res_id = 3;
name | unknown
---+---
foo | 1
wheyho | 2
dong | 1
Is there any way to bring these single results together with one SELECT statements ?With SUMs and CASEs:
SELECT
name,
SUM(CASE WHEN res_id = 1 THEN 1 ELSE 0 END) as pass
SUM(CASE WHEN res_id = 2 THEN 1 ELSE 0 END) as fail
SUM(CASE WHEN res_id = 3 THEN 1 ELSE 0 END) as unknown
FROM t1
GROUP BY name
Untested, syntax may be off.|||Wow, this was a fast reply! And, what is more, a very good one. It works just the way I was looking for !
thanks a lot !