SQL Counting All Rows Instead of Counting Individual Rows

SQL counting all rows instead of counting individual rows

You're missing GROUP BY, so it's counting everything instead of grouping by some columns.

LEFT JOIN
(
    select `ID` as `AuraID`, `Status` as `AuraStatus`, count(*) as `Aura`
    from messages_aura
    GROUP BY AuraID, AuraStatus
) aura ON (var.Poster = aura.AuraID AND var.ID = aura.AuraStatus)

Count rows with specific entry for every distinct entry of another row

You seems want conditional aggregation :

select Year_CW,
       sum(case when col = 1 then 1 else 0 end) as one_count,
       sum(case when col = 0 then 1 else 0 end) as zero_count
from (select Year_CW, IsStarted as col
      from TestView tv
      union all
      select Year_CW, NeedsHelp
      from TestView tv
     ) tv
group by Year_CW
order by Year_CW desc;

Two separate queries; one needs to count rows from the other

If your 'Trailer' query only ever returns 1 record... then you can try to use the tsql function @@recordcount

Like this:

-- Declare an integer variable
DECLARE @rc as integer;

-- Your Detail query
SELECT
   IDont
   ,CareWhat
   ,TheDetailLogicIs
FROM
   Inv00101 as i
   JOIN WhoKnowsWhat as idk ON i.ITEMNMBR = idk.ITEMNMBR
WHERE 
   Who = Cares;

-- Assign your row count variable with the row count of the last executed query.
SET @rc = @@rowcount;

-- Your new Trailer query
SELECT
   'TRL' AS [RECID]
   ,@rc AS [TOTREC];

This has the added benefit of giving you the row count of the actual query you are interested in having the count of. Plus you don't have to execute duplicate logic which could be time consuming (especially if your Detail query is as complicated as you make it out to be).

Hope that helps :)

The SUM() is counting all the values, instead of the ones that meet the condition

I find the question rather hard to follow, not understanding what the column names are. I don't think you need aggregation to solve this, but you need a way to calculate the total -- and window functions are one method:

SELECT (t.Imie + ' ' + t.Nazwisko) AS ImieNazwisko,
       (WartoscRynkowa - KwotaTransferu) as Result,
       SUM(WartoscRynkowa - KwotaTransferu) OVER () as overall_total
FROM Beata.dane D JOIN 
     Beata.team AS T
     ON D.NumerKoszulki = T.NumerKoszulki
WHERE SzansaNaTransfer >= 2;

SQL query for finding records where count 1

Use the HAVING clause and GROUP By the fields that make the row unique

The below will find

all users that have more than one payment per day with the same account number

SELECT 
 user_id ,
 COUNT(*) count
FROM 
 PAYMENT
GROUP BY
 account,
 user_id ,
 date
HAVING
COUNT(*) > 1

Update
If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT 
    user_id,
    account_no , 
    date,
        COUNT(*)
 FROM
    (SELECT DISTINCT
            user_id,
            account_no , 
            zip, 
            date
         FROM
            payment 
    
        ) 
        payment
 GROUP BY
    
    user_id,
    account_no , 
    
    date
HAVING COUNT(*) > 1

Counting all rows of table once, after a series of Union Alls

The way you've laid your query out is creating a lot of extra effort for both you and the SQL Server.

First, You can do most of in in a single query, if you adopt a very slightly different structure using "conditional aggregation"; that is COUNT() with a CASE expression inside it.

Then you can simply add another column for the TOTAL_COUNT. Though, I'm not entirely sure what you want for that total, so I've included a few options...

SELECT
  CAST('DB_NAME_HERE') AS VARCHAR(255)) AS DB_NAME,

  CAST('1STCOLUMN')    AS VARCHAR(255))                   AS RULE_ID_1_COLUMN_NAME,
  COUNT(CASE WHEN [Name of column 1] IS NULL THEN 1 END)  AS RULE_ID_1_DEFECT_COUNT,

  CAST('2NDCOLUMN')    AS VARCHAR(255))                   AS RULE_ID_2_COLUMN_NAME,
  COUNT(CASE WHEN [Name of column 2] IS NULL THEN 1 END)  AS RULE_ID_2_DEFECT_COUNT,

  CAST('3RDCOLUMN')    AS VARCHAR(255))                   AS RULE_ID_3_COLUMN_NAME,
  COUNT(CASE WHEN [Name of column 3] IS NULL THEN 1 END)  AS RULE_ID_3_DEFECT_COUNT,

  COUNT(*)                                                AS TOTAL_DAILY_ROWS,
  COUNT(CASE WHEN [Name of column 1] IS NULL
               OR [Name of column 2] IS NULL 
               OR [Name of column 3] IS NULL THEN 1 END)  AS TOTAL_DAILY_ROWS_WITH_NULLS,
  (SELECT COUNT(*) FROM [Name of Db])                     AS TABLE_TOTAL_ROWS
FROM
  [Name of Db]
WHERE
  DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))

Then, if you really must have your existing format, you can un-pivot it...

WITH
  yesterday_summary AS
(
  SELECT
    CAST('DB_NAME_HERE') AS VARCHAR(255)) AS DB_NAME,
    COUNT(CASE WHEN [Name of column 1] IS NULL THEN 1 END)  AS RULE_ID_1_DEFECT_COUNT,
    COUNT(CASE WHEN [Name of column 2] IS NULL THEN 1 END)  AS RULE_ID_2_DEFECT_COUNT,
    COUNT(CASE WHEN [Name of column 3] IS NULL THEN 1 END)  AS RULE_ID_3_DEFECT_COUNT
  FROM
    [Name of Db]
  WHERE
    DateField = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
)
SELECT
  y.DB_NAME,
  p.RULE_ID,
  p.COLUMN_NAME,
  p.DEFECT_COUNT,
  (SELECT COUNT(*) FROM [Name of Db])   AS TABLE_TOTAL_ROWS
FROM
  yesterday_summary   AS y
CROSS APPLY
(
  VALUES
    ('RULE_ID_1', '1STCOLUMN', y.RULE_ID_1_DEFECT_COUNT),
    ('RULE_ID_2', '2NDCOLUMN', y.RULE_ID_2_DEFECT_COUNT),
    ('RULE_ID_3', '3RDCOLUMN', y.RULE_ID_3_DEFECT_COUNT)
)
  AS p(RULE_ID, COLUMN_NAME, DEFECT_COUNT)

Count numbers in single row - SQL

You want SUM instead of COUNT. COUNT merely counts the number of records, you want them summed.

You didn't mention your DBMS, but see for example, for sql server this

how to get all row count of a table from rowcount after having top row filter

One way to do it would be something like this:

SELECT 
    (your list of columns),
    COUNT(*) OVER ()       
FROM 
    dbo.YourTable
ORDER BY
    (whatever column you want to order by)
    OFFSET x ROWS FETCH NEXT y ROWS ONLY;

With the OFFSET / FETCH, you retrieve only a page of data - and the COUNT(*) OVER() will give you the total count of the rows in the table - all in a single query

Query returns multiple rows for a single count

I'd prefer sample data, but - if you are satisfied with the current query and it does what you want it to but would also want to get rid of the count = 0 rows if there are rows whose count values aren't 0, then a simple option is

SQL> with test (account_num, le_name, cnt) as
  2  -- this represents result of your current query
  3    (select 'A', 'A LE',  0 from dual union all
  4     select 'B', 'B LE',  0 from dual union all
  5     select 'B', 'B LE', 17 from dual union all
  6     select 'C', 'C LE',  0 from dual union all
  7     select 'C', 'C LE',  1 from dual
  8    )
  9  -- query you might need
 10  select account_num,
 11         le_name,
 12         sum(cnt) cnt
 13  from test
 14  group by account_num,
 15           le_name
 16  order by account_num;

A LE_NAME          CNT
- --------- ----------
A A LE               0
B B LE              17
C C LE               1

SQL>

---

Related Topics