Sql Select Count for Multiple Columns in a Single Query

SQL Select COUNT for Multiple Columns in a Single Query

You could do this if your sql platform supports the CASE statement.

SELECT SUM(CASE WHEN Col1 = 'a' THEN 1 ELSE 0 END) as '# of a'
    ,  SUM(CASE WHEN Col2 = 'dog' THEN 1 ELSE 0 END) as '# of dogs'
FROM "Table 1"

The above query uses SUM instead of COUNT and the inner expression returns 0 or 1 depending on if the condition evaluates to false or true.

Select count of multiple columns WHERE another column is distinct

Use conditional aggregation:

SELECT COUNT(DISTINCT CASE WHEN Hamburger IS NOT NULL THEN Id END) AS HamburgerCount,
       COUNT(DISTINCT CASE WHEN Fries IS NOT NULL THEN Id END) AS FriesCount,
       COUNT(DISTINCT CASE WHEN Soda IS NOT NULL THEN Id END) AS SodaCount
FROM tablename;

select multiple columns count one column and group by one column in one table

If you want the last date along with the count, you can use window functions:

select d.Employee, d.cnt, d.Post, d.c_name, d.Deli_Date, d.Note 
from (select d.*,
             count(*) over (partition by employee) as cnt,
             row_number() over (partition by employee order by deli_date desc) as seqnum
      from delivery d
     ) d
where seqnum = 1;

SELECT count of multiple columns with a WHERE clause

Use conditional aggregation with a CASE expression, something along these lines:

SELECT
    some_id,
    COUNT(CASE WHEN rank = 1 AND level = 30 THEN 1 END) AS bronze,
    COUNT(CASE WHEN rank = 2 AND level = 45 THEN 1 END) AS silver
FROM stats
GROUP BY some_id;

If you really want to take a tally over the entire table, then you don't need to use GROUP BY.

How to Select count of multiple columns

You can need to do unpivot & pivot :

SELECT Field, 
       SUM(CASE WHEN choice = '1stChoice' THEN 1 ELSE 0 END) AS [1stChoice],
       SUM(CASE WHEN choice = '2Choice' THEN 1 ELSE 0 END) AS [2stChoice],
       SUM(CASE WHEN choice = '3rdChoice' THEN 1 ELSE 0 END) AS [3stChoice]
FROM myView mv CROSS APPLY
     ( VALUES ('1stChoice', [1stChoice]), ('2Choice', [2Choice]), ('3rdChoice', [3rdChoice])
     ) mvv (choice, Field)
GROUP BY Field;

Use COUNT() for multiple columns over several years

It's tricky when you need different constraints to aggregate what you want. I wouldn't use count, I would sum the instances instead with CASE statements. Here is an example you can run in your session on SQL Server:

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP(
    WellType            NVARCHAR(10)
    ,EventDate          DATE
)
INSERT INTO #TEMP (WellType, EventDate)
VALUES ('OW','2021-11-03')
    ,('GW','2020-11-03')
    ,('D','2019-11-03')
    ,('OWI','2018-11-03')
    ,('WI','2017-11-03')
    ,('WI','2021-11-03')
    ,('D','2020-11-03')
    ,('D','2019-11-03')
    ,('GW','2018-11-03')
    ,('OW','2017-11-03')
    ,('OW','2021-11-03')
    ,('GW','2020-11-03')
    ,('D','2019-11-03')
    ,('OWI','2018-11-03')
    ,('WI','2017-11-03')
    ,('WI','2021-11-03')
    ,('D','2020-11-03')
    ,('D','2019-11-03')
    ,('GW','2018-11-03')
    ,('OW','2017-11-03')

SELECT 
    WellType
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) [THIS YEAR]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-1,GETDATE())) THEN 1 ELSE 0 END) [LAST YEAR]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-2,GETDATE())) THEN 1 ELSE 0 END) [2 YEARS AGO]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-3,GETDATE())) THEN 1 ELSE 0 END) [3 YEARS AGO]
FROM #TEMP
GROUP BY WellType

I also use the GETDATE() function to establish what year to sum. This should do what you want.

OUTPUT BEFORE AND AFTER SELECT STATEMENT:

How to do count(distinct) for multiple columns

[TL;DR] Just use a sub-query.

---

If you are trying to use concatenation then you need to ensure that you delimit the terms with a string that is never going to appear in the values otherwise you will find non-distinct terms grouped together.

For example: if you have a two numeric column then using COUNT(DISTINCT col1 || col2) will group together 1||23 and 12||3 and count them as one group.

You could use COUNT(DISTINCT col1 || '-' || col2) but if the columns are string values and you have 'ab-'||'-'||'c' and 'ab'||'-'||'-c' then, once again, they would be identical once concatenated.

The simplest method is to use a sub-query.

If you can't do that then you can combine columns via string-concatenation but you need to analyse the contents of the column and pick a delimiter that does not appear in your strings otherwise your results might be erroneous. Even better is to ensure that the delimiter character will never be in the sub-string with check constraints.

ALTER TABLE mytable ADD CONSTRAINT mytable__col1__chk CHECK (col1 NOT LIKE '%¬%');
ALTER TABLE mytable ADD CONSTRAINT mytable__col2__chk CHECK (col2 NOT LIKE '%¬%');

Then:

SELECT COUNT(DISTINCT col1 || '¬' || col2)
FROM   mytable;

---

Related Topics