• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34
  • Last Modified:

sql results as single table

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

Open in new window

i need to check all the way to LineNum = 20
0
csePixelated
Asked:
csePixelated
4 Solutions
 
Bill PrewCommented:
How do you want the output to look?


»bp
0
 
csePixelatedAuthor Commented:
A single column named 'Call Count' numbered with rows 1-20.
0
 
Bill PrewCommented:
Okay, give this a try:

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

Open in new window


»bp
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Bill PrewCommented:
Actually, slight adjustment to that...

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

Open in new window


»bp
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Bill's code above does the job.

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

Open in new window

0
 
Bill PrewCommented:
And if you are actually counting all the LineNum's, not just selective ones, then you could do:

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

Open in new window

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;

Open in new window


»bp
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>A single column named 'Call Count' numbered with rows 1-20.
Not 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

Open in new window

(post code edit)  Jinx
0
 
Scott PletcherSenior DBACommented:
Best way is with just a single pass of the main table, for efficiency (and ease of coding, perhaps).

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].[SignalHistory]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now