SQL COUNT with Multiple Columns

Question

I have an application that calls a Web Service, I can potentially pass up to 5 ID's to this server and return information.

I create an 'Audit' line in a SQL table to keep a record of all the information submitted.

The ID's can be submitted in ANY order, the columns are

DLN0,DLN1,DLN2,DLN3,DLN4.

I also have DateTime and Username Columns, what I want to try and do is COUNT the number of times an ID has been entered on any given day in any of the 5 columns, because I don't want someone submitting the same ID to the service more than twice (In ANY of the given columns).

I think I could do it by UNION but it makes it very messy to accomplish, or is this the only way to go?

I have done this with some success

SELECT COUNT(MyTbl.DLN0) AS [Count], MyTbl.DLN0 AS [DLN], MyTbl.Username, CONVERT(VARCHAR(12),MyTbl.RequestDT,103) AS [DateTime] FROM 
(
SELECT DLN0, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION
SELECT DLN1, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION
SELECT DLN2, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION
SELECT DLN3, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION
SELECT DLN4, [RequestDT], Username FROM dbo.MyL_Audit_Log
) AS MyTbl
WHERE MyTbl.DLN0 <> ''
GROUP BY MyTbl.DLN0, MyTbl.Username, CONVERT(VARCHAR(12),MyTbl.RequestDT,103)

My Table:

CREATE TABLE [dbo].[MyL_Audit_Log](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Username] [VARCHAR](30) NOT NULL,
    [IP Address] [VARCHAR](15) NOT NULL,
    [RequestDT] [DATETIME] NOT NULL,
    [TranType] [VARCHAR](20) NOT NULL,
    [NoOfDrivers] [INT] NOT NULL,
    [DLN0] [VARCHAR](30) NOT NULL,
    [PCode] [VARCHAR](15) NULL,
    [DLN1] [VARCHAR](30) NULL,
    [DLN2] [VARCHAR](30) NULL,
    [DLN3] [VARCHAR](30) NULL,
    [DLN4] [VARCHAR](30) NULL,
    [RequestString] [VARCHAR](MAX) NOT NULL,
    [Result] [VARCHAR](30) NOT NULL,
    [HttpStatusCode] [VARCHAR](10) NOT NULL,
    [ResponseTime] [INT] NOT NULL,
    [ReturnedData] [VARCHAR](MAX) NOT NULL,
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] TEXTIMAGE_ON [PRIMARY]

Sample ID's

XYZ43562MC4 ABC23214MC2 LKU23411PO3

Cheers.

Please could you add SQL definitions of the structure and your attempted query? — Chris Pickford, Jan 20 '16 at 17:11
why do you need union here ? is there any issue with this query SELECT COUNT(MyTbl.DLN0) AS [Count], MyTbl.DLN0 AS [DLN], MyTbl.Username, CONVERT(VARCHAR(12),MyTbl.RequestDT,103) AS [DateTime] FROM dbo.MyL_Audit_Log as MyTbl WHERE MyTbl.DLN0 <> '' GROUP BY MyTbl.DLN0, MyTbl.Username, CONVERT(VARCHAR(12),MyTbl.RequestDT,103) — Arunprasanth K V, Jan 20 '16 at 17:41
@ArunprasanthKV because I have DLN1, DLN2, DLN3, DLN4 where the ID can also appear. — Lynchie, Jan 20 '16 at 17:49
can you provide some sample data, its little bit confusing your requirement , i hope you dont need union for taking the sum of counts — Arunprasanth K V, Jan 20 '16 at 18:13

score 1 · Accepted Answer · answered Jan 20 '16 at 17:27

1

Your query is fine, but you may want to use Union ALL instead of Union (A UNION statement does a SELECT DISTINCT on the results set.):

SELECT COUNT(MyTbl.DLN0) AS [Count], MyTbl.DLN0 AS [DLN], MyTbl.Username, CONVERT(VARCHAR(12),MyTbl.RequestDT,103) AS [DateTime] FROM 
(
SELECT DLN0, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION ALL
SELECT DLN1, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION ALL
SELECT DLN2, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION ALL
SELECT DLN3, [RequestDT], Username FROM dbo.MyL_Audit_Log
UNION ALL
SELECT DLN4, [RequestDT], Username FROM dbo.MyL_Audit_Log
) AS MyTbl
WHERE MyTbl.DLN0 <> ''
GROUP BY MyTbl.DLN0, MyTbl.Username, CONVERT(VARCHAR(12),MyTbl.RequestDT,103)

answered Jan 20 '16 at 17:27

Haytem BrB

1,528
3
16
23

why he need union here ? is there issue if directly taken the count ?? – Arunprasanth K V Jan 20 '16 at 17:45
What I understood is that he wants the count of DLN0+DLN1+DLN2+DLN3+DLN4, and Union All is a quick a good solution for this. – Haytem BrB Jan 20 '16 at 17:51
@Lynchie, what makes you say that your query is messy? I think the query is fine – Haytem BrB Jan 20 '16 at 17:56
@haytem I thought it was just a lot of lines for what I'm trying to accomplish but I guess I was on the money with it anyway - I guess its doubting myself. – Lynchie Jan 20 '16 at 18:01
Your query is correct and acceptable for a small number of columns (DLNs). You can also use Unpivot as shown here: http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows – Haytem BrB Jan 20 '16 at 18:08
@ArunprasanthKV: let's take an example where a user calls the webservice with 5 DLNs, and where DLN0=DLN1. In this case Union will discard one of them as it returns distinct rows, but with Union all we will have the correct count. Plus, when you have the choise, better use Union All as it is always quicker. – Haytem BrB Jan 21 '16 at 10:01
ok i am little bit unclear about the questioer's goal, hope your query will help him – Arunprasanth K V Jan 21 '16 at 10:22

SQL COUNT with Multiple Columns

1 Answers1