SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query for practice and learning. (?)		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MacJK
Starting Member

Germany
24 Posts
Posted - 08/07/2010 :  07:13:24  Show Profile  Reply with Quote
Hi SQL Team Forum members and Readers!

My SQL skills are not the best but I make process. I create a query in the SQL Designer but don’t get the result I aspect.
Should I run a sub query inside with a Derivade Table?


SELECT     COUNT(*) AS counter, ItemGroup, status
FROM         Table_1
WHERE     (status = 0)
GROUP BY ItemGroup, status


Result i aspact:

a = 0
b = 0


id          name                                               itemgroup                                          status
----------- -------------------------------------------------- -------------------------------------------------- ------
1           a1                                                 a                                                  0
2           a2                                                 a                                                  0
3           a3                                                 a                                                  0
4           b1                                                 b                                                  0
5           b2                                                 b                                                  0
6           b3                                                 b                                                  0
7           c1                                                 c                                                  1
8           c2                                                 c                                                  0
9           c3                                                 c                                                  0
10          d1                                                 d                                                  1
11          d2                                                 d                                                  1
12          d3                                                 d                                                  0
13          d4                                                 d                                                  0

(13 row(s) affected)

counter     itemgroup                                          status
----------- -------------------------------------------------- ------
3           a                                                  0
3           b                                                  0
2           c                                                  0
2           d                                                  0

(4 row(s) affected)



Some more Information:

Group a have thre items a1, a2 and a3 ale are false this mean Group a is false.

Create script:


CREATE TABLE [dbo].[Table_1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[ItemGroup] [nvarchar](50) NULL,
	[status] [bit] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Table_1] ON
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (1, N'A1', N'A', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (2, N'A2', N'A', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (3, N'A3', N'A', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (4, N'B1', N'B', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (5, N'B2', N'B', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (6, N'B3', N'B', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (7, N'C1', N'C', 1)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (8, N'C2', N'C', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (9, N'C3', N'C', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (10, N'D1', N'D', 1)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (11, N'D2', N'D', 1)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (12, N'D3', N'D', 0)
INSERT [dbo].[Table_1] ([id], [Name], [ItemGroup], [status]) VALUES (13, N'D4', N'D', 0)
SET IDENTITY_INSERT [dbo].[Table_1] OFF


Many thanks for help and point to some solutions.

This for practice and learning.



br
Jaroslaw

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts
Posted - 08/07/2010 :  07:32:25  Show Profile  Reply with Quote
what is your desired output?
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts
Posted - 08/07/2010 :  07:37:53  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
i think he expects like 
Result 
a = 0
b = 0


With Regards
Kashyap M
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts
Posted - 08/07/2010 :  07:40:13  Show Profile  Reply with Quote
anyways, try this:


select 
	itemgroup
	,sum(cast(status as int)) as status
from table_1
group by itemgroup
having sum(cast(status as int)) = 0
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts
Posted - 08/07/2010 :  07:41:14  Show Profile  Reply with Quote
yes if he expects this result for query to return only itemgroups that have only status = 0 than the above query should do the trick.

best
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts
Posted - 08/07/2010 :  08:00:30  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
I HAD CHECKED THE OUTPUT CAME IF HE EXPECTS THE SAME OR NOT I AM NOT SURE ABOUT THAT BUT IT WORKS NICE WORK

With Regards
Kashyap M
Go to Top of Page

MacJK
Starting Member

Germany
24 Posts
Posted - 08/08/2010 :  06:12:01  Show Profile  Reply with Quote
Hi slimt_slimt and kashyap_sql,

the select command work like i aspect. Many thanks. It pushes me forward.
What is the differents between HAVING and WHERE or are this different function?

br
Jaroslaw


br
Jaroslaw
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts
Posted - 08/08/2010 :  08:31:22  Show Profile  Reply with Quote
good question.
in case of this query HAVING statement is specified for searching based on grouped or aggregated values. and if there is no GROUP BY statement defined with HAVING statement, HAVING statement would behave more or less like WHERE statement.

WHERE statement specifies a filter for returning rows for following SQL statements: SELECT (also INSERT with combination of SELECT), UPDATE and DELETE.
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts
Posted - 08/09/2010 :  01:12:09  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/
go through the link you will understand main diff b/w where and having

With Regards
Kashyap M
Go to Top of Page

danieljackson
Starting Member

3 Posts
Posted - 08/09/2010 :  08:21:56  Show Profile  Reply with Quote
select
itemgroup
,sum(cast(status as int)) as status
from table_1
group by itemgroup
having sum(cast(status as int)) = 0
Edited by - tkizer on 08/10/2010 01:07:17
Go to Top of Page

kashyap_sql
Posting Yak Master

India
174 Posts
Posted - 08/10/2010 :  01:12:16  Show Profile  Visit kashyap_sql's Homepage  Reply with Quote
quote:
Originally posted by danieljackson

select
itemgroup
,sum(cast(status as int)) as status
from table_1
group by itemgroup
having sum(cast(status as int)) = 0



whats the d/w b/w urs and slimt_slimt they are same na

With Regards
Kashyap M
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000