How to write query get counts of part by two fields onlinedata and offlinedata from general table ?

Question

0.00/5 (No votes)

See more:

, +

problem

How to write query get counts of part by two fields OnlineData and OfflineData from General Table ?

Result Expected

SQL

OnlineData      OfflineData          CountParts
www.spare.com    www.sic.vom             3
www.while.com    www.datacomer.com       0

I need to write query get count number of parts per every OnlineData and OfflineData that have 'Coca' text on

Accepted value table from table General and not have 'Coca' text on accepted value table from table Reach

based on PartId and SourceType

meaning i need count parts have Coca text from General Table and same parts not have 'Coca' text from table Reach .

AS Example PartId : 200 have 'Coca' text on Accepted value from General Table and not have 'Coca' text from table Reach that actually that i need to count it

if nothing then count of parts will be 0

the join between (reach table and General table) AND accepted value is sourcetype=Accptedvlueid

the join between reach table and General Table PartId .

What I have tried:

USE [Store]

CREATE TABLE [dbo].[AcceptedValue](
	[AcceptedId] [int] NOT NULL,
	[AcceptedName] [nvarchar](50) NULL,
 CONSTRAINT [PK_AcceptedValue] PRIMARY KEY CLUSTERED 
(
	[AcceptedId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[General](
	[GeneralId] [int] NOT NULL,
	[PartId] [int] NULL,
	[SourceType] [nvarchar](50) NULL,
	[OnlineData] [nvarchar](200) NULL,
	[OfflineData] [nvarchar](200) NULL,
 CONSTRAINT [PK_General] PRIMARY KEY CLUSTERED 
(
	[GeneralId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Reach](
	[ReachId] [int] NULL,
	[PartId] [int] NULL,
	[SourceType] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (500, N'CocaDone')
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (502, N'CocaJes')
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (510, N'ComSpare')
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (512, N'ColNumber')
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (550, N'KidleAtlanta')
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (560, N'Helogin')
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (900, N'CocaHard')
INSERT [dbo].[AcceptedValue] ([AcceptedId], [AcceptedName]) VALUES (905, N'CodSeries')
INSERT [dbo].[General] ([GeneralId], [PartId], [SourceType], [OnlineData], [OfflineData]) VALUES (120, 200, N'900', N'www.spare.com', N'www.sic.vom')
INSERT [dbo].[General] ([GeneralId], [PartId], [SourceType], [OnlineData], [OfflineData]) VALUES (125, 277, N'500', N'www.spare.com', N'www.sic.vom')
INSERT [dbo].[General] ([GeneralId], [PartId], [SourceType], [OnlineData], [OfflineData]) VALUES (129, 299, N'502', N'www.spare.com', N'www.sic.vom')
INSERT [dbo].[General] ([GeneralId], [PartId], [SourceType], [OnlineData], [OfflineData]) VALUES (130, 250, N'550', N'www.while.com', N'www.datacomer.com')
INSERT [dbo].[Reach] ([ReachId], [PartId], [SourceType]) VALUES (120, 200, N'905')
INSERT [dbo].[Reach] ([ReachId], [PartId], [SourceType]) VALUES (125, 277, N'510')
INSERT [dbo].[Reach] ([ReachId], [PartId], [SourceType]) VALUES (129, 299, N'512')
INSERT [dbo].[Reach] ([ReachId], [PartId], [SourceType]) VALUES (130, 250, N'560')

Posted 16-Dec-19 13:56pm

ahmed_sa

Updated 16-Dec-19 22:04pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-12-16T22:05:00

When I approach problems like this I try to identify the key data that I need to know, in this case it's

does the Accepted Name contain 'coca' or not?

Then I try to find a way of pulling out just that key data - without worrying about anything else. In this case I could do

SQL

select G.OnlineData, G.OfflineData,  
          CASE WHEN AcceptedName LIKE '%Coca%' THEN 1 else 0 END as includepart
from General G
left outer join AcceptedValue AV on AV.AcceptedId=G.SourceType

Note that the Reach table isn't relevant to this so I've ignored it. That code is going to give me the results

OnlineData	OfflineData	includepart
www.spare.com	www.sic.vom		1
www.spare.com	www.sic.vom		1
www.spare.com	www.sic.vom		1
www.while.com	www.datacomer.com	0

Instead of 1 and 0 I could just as easily have used 'Yes' and 'No' but 1 and 0 make the next step really easy.

I need to have those results somewhere in order to use them in the next step, which is going to be to use GROUP BY [OnlineData], [OfflineData] and to SUM that interim column [includepart]

I could use a Temporary table ...

SQL

if OBJECT_ID('tempdb..#interim_step', 'U') is not null drop table #interim_step
select G.OnlineData, G.OfflineData,  CASE WHEN AcceptedName LIKE '%Coca%' THEN 1 else 0 END as includepart
into #interim_step
from General G
left outer join AcceptedValue AV on AV.AcceptedId=G.SourceType

Or, I could use a table variable

SQL

declare @interim_step table ([OnlineData] [nvarchar](200) NULL,	[OfflineData] [nvarchar](200) NULL, includepart int NOT NULL)
insert into @interim_step
select G.OnlineData, G.OfflineData,  CASE WHEN AcceptedName LIKE '%Coca%' THEN 1 else 0 END as includepart
from General G
left outer join AcceptedValue AV on AV.AcceptedId=G.SourceType

Or I could use my personal preference, a Common Table Expression (see Common Table Expressions (Introduction to CTE's) - Essential SQL[^])

SQL

;with CTE AS
(
	select G.OnlineData, G.OfflineData,  CASE WHEN AcceptedName LIKE '%Coca%' THEN 1 else 0 END as includepart
	from General G
	left outer join AcceptedValue AV on AV.AcceptedId=G.SourceType
)
SELECT OnlineData, OfflineData, sum(includepart) as [count]
FROM CTE
GROUP BY OnlineData, OfflineData
ORDER BY OnlineData, OfflineData

Point to note: Spot the semi-colon before the declaration of the CTE? If you omit it and have other code before the CTE you will get an error

Error:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

If you make it a habit to always use ;WITH you'll not only avoid the error when you first write the code, you will also make it easy to insert new code during subsequent maintenance