Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

I am trying to get a simple table of results showing the number of times we have entries meeting the criteria below, when i run the below i get each result in a separate result payne how do i get the count in a simple table?

```
SELECT count (*)
FROM [History0218].[dbo].[SignalHistory]
WHERE PortNum = 3 and LineNum = 1
SELECT count (*)
FROM [History0218].[dbo].[SignalHistory]
WHERE PortNum = 3 and LineNum = 2
SELECT count (*)
FROM [History0218].[dbo].[SignalHistory]
WHERE PortNum = 3 and LineNum = 3
```

i need to check all the way to LineNum = 20
Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

```
SELECT LineNum, COUNT(*) AS [Call Count] FROM [History0218].[dbo].[SignalHistory] WHERE PortNum = 3 and LineNum = 1
UNION
SELECT LineNum, COUNT(*) AS [Call Count] FROM [History0218].[dbo].[SignalHistory] WHERE PortNum = 3 and LineNum = 2
UNION
SELECT LineNum, COUNT(*) AS [Call Count] FROM [History0218].[dbo].[SignalHistory] WHERE PortNum = 3 and LineNum = 3
```

»

```
SELECT MAX(LineNum), COUNT(*) AS [Call Count] FROM [History0218].[dbo].[SignalHistory] WHERE PortNum = 3 and LineNum = 1
UNION
SELECT MAX(LineNum), COUNT(*) AS [Call Count] FROM [History0218].[dbo].[SignalHistory] WHERE PortNum = 3 and LineNum = 2
UNION
SELECT MAX(LineNum), COUNT(*) AS [Call Count] FROM [History0218].[dbo].[SignalHistory] WHERE PortNum = 3 and LineNum = 3
```

»

Another style I use, especially if these queries take a long time to run and you don't want to deal with locking/blocking, is this...

```
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp (label VARCHAR(100), row_count INT)
INSERT INTO #tmp (label, row_count)
SELECT 'WHERE PortNum = 3 and LineNum = 1', COUNT (*)
FROM [History0218].[dbo].[SignalHistory]
WHERE PortNum = 3 and LineNum = 1
INSERT INTO #tmp (label, row_count)
SELECT 'WHERE PortNum = 3 and LineNum = 2', COUNT (*)
FROM [History0218].[dbo].[SignalHistory]
WHERE PortNum = 3 and LineNum = 2
INSERT INTO #tmp (label, row_count)
SELECT 'PortNum = 3 and LineNum = 3', COUNT (*)
FROM [History0218].[dbo].[SignalHistory]
WHERE PortNum = 3 and LineNum = 3
SELECT * FROM #tmp
```

```
SELECT LineNum, COUNT(*) AS [Call Count] FROM [History0218].[dbo].[SignalHistory] WHERE PortNum = 3 GROUP BY LineNum;
```

And if you need to select just certain LineNum's then perhaps:```
SELECT LineNum, COUNT(*) AS [Call Count]
FROM [History0218].[dbo].[SignalHistory]
WHERE PortNum = 3
AND LineNum IN (1,2,3)
GROUP BY LineNum
ORDER BY LineNum;
```

»

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialNot really certain what the 'rows 1-20' means but would something like this work?

```
SELECT LineNum, COUNT(LineNum) as the_count
FROM [History0218].[dbo].[SignalHistory]
GROUP BY LineNum
WHERE PortNum = 3 AND LineNum >= 1 AND LineNum <= 20
ORDER BY LineNum
```

(post code edit) Jinx
If you need separate rows, just let me know, and I'll give you a cross tab query to convert this row to multiple rows.

SELECT

SUM(CASE WHEN PortNum = 3 and LineNum = 1 THEN 1 ELSE 0 END) AS Port3_Line1_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 2 THEN 1 ELSE 0 END) AS Port3_Line2_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 3 THEN 1 ELSE 0 END) AS Port3_Line3_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 4 THEN 1 ELSE 0 END) AS Port3_Line4_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 5 THEN 1 ELSE 0 END) AS Port3_Line5_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 6 THEN 1 ELSE 0 END) AS Port3_Line6_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 7 THEN 1 ELSE 0 END) AS Port3_Line7_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 8 THEN 1 ELSE 0 END) AS Port3_Line8_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 9 THEN 1 ELSE 0 END) AS Port3_Line9_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 10 THEN 1 ELSE 0 END) AS Port3_Line10_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 11 THEN 1 ELSE 0 END) AS Port3_Line11_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 12 THEN 1 ELSE 0 END) AS Port3_Line12_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 13 THEN 1 ELSE 0 END) AS Port3_Line13_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 14 THEN 1 ELSE 0 END) AS Port3_Line14_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 15 THEN 1 ELSE 0 END) AS Port3_Line15_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 16 THEN 1 ELSE 0 END) AS Port3_Line16_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 17 THEN 1 ELSE 0 END) AS Port3_Line17_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 18 THEN 1 ELSE 0 END) AS Port3_Line18_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 19 THEN 1 ELSE 0 END) AS Port3_Line19_Count,

SUM(CASE WHEN PortNum = 3 and LineNum = 20 THEN 1 ELSE 0 END) AS Port3_Line20_Count

/*, SUM(CASE WHEN PortNum = 3 AND LineNum BETWEEN 1 AND 20 THEN 1 ELSE 0 END) AS Port3_All_Lines_Total */

FROM [History0218].[dbo].[Signa

Query Syntax

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

»

bp