SQL Query for practice and learning. (?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query for practice and learning. (?)

Author

Topic

MacJK
Starting Member

24 Posts

Posted - 2010-08-07 : 07:13:24

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

746 Posts

Posted - 2010-08-07 : 07:32:25

what is your desired output?

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-07 : 07:37:53

i think he expects like

Result 
a = 0
b = 0

With Regards
Kashyap M

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-07 : 07:40:13

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-07 : 07:41:14

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

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-07 : 08:00:30

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

MacJK
Starting Member

24 Posts

Posted - 2010-08-08 : 06:12:01

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 08:31:22

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.

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-09 : 01:12:09

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

danieljackson
Starting Member

3 Posts

Posted - 2010-08-09 : 08:21:56

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

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-10 : 01:12:16

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources