SQL 2 Counts with Different Filter

SQL 2 counts with different filter

This is the same idea as tombom's answer, but with SQL Server syntax:

SELECT
    SUM(CASE WHEN CoumntA < 0 THEN 1 ELSE 0 END) AS LessThanZero,
    SUM(CASE WHEN CoumntA > 0 THEN 1 ELSE 0 END) AS GreaterThanZero
FROM TableA

Combine two queries to count distinct strings with different filters

Much faster and simpler with conditional aggregates using the aggregate FILTER clause:

SELECT source
     , count(DISTINCT sku) FILTER (WHERE product_gap = 'yes') AS yes_gap
     , count(DISTINCT sku) FILTER (WHERE product_gap = 'no')  AS no_gap
FROM   product_gaps
WHERE  ingestion_date <= '2021-05-25'
GROUP  BY source;

See:

• Aggregate columns with additional (distinct) filters

Aside 1: DISTINCT is a key word, not a function. Don't add parentheses for the single column. distinct(sku) is short notation for DISTINCT ROW(sku). It happens to work because Postgres strips the ROW wrapper for a single column, but it's just noise.

Aside 2: product_gap should probably be boolean.

How to count products to multiple filters separated by group

(What is the "question"?)

I think the best way to implement such is to build a query based on the checkboxes, then process the data in a single pass in your application language.

Side issue: Switch from MyISAM to InnoDB.

Side issue: Shrink INT (which takes 4 bytes) to smaller datatypes.

The point behind these side issues is performance and space. Many of the generated queries will involve a full table scan, filtering as it goes. (That is, INDEXes will not be useful much of the time.)

The user can click multiple boxes in each grouping, correct? Then consider having, for example, TINYINT UNSIGNED, which has 8 bits (in 1 byte) to handle up all combinations of up to 8 choices. For example, instead of

AND size_id IN (0, 1)   -- 0 means '65in+'; 1 means '50-65in'

do

AND ((size_opts & 0x3) = 0x3)

That is, the bottom bit of size_opts represents '65in+', etc.

This would shrink the dataset quite a bit and require different pre-processing to generate the queries.

Note that the value of your size_id is 0..4, my size_opts would have some or all of the bottom 5 bits on.

ORing together (1 << size_id) values gets you from size_id to size_opts.

(More)

There are 2 things going on:

• Filtering (limiting the display by brand/price/color/whatever)
• Counting how many are still in the running (by each remaining criteria)

To goals are needed to make it somewhat efficient:

• Shrink the dataset as much as possible
• Don't include in the table any items that are 'deleted' etc. Don't include any columns that are not relevant to the filtering and counting. They fight with the shrinkage goal
• Shrink the categorization as much as possible. The "bit fiddling" I suggested may be optimal.
• Build the query dynamically. Leave out unnecessary tests. (Example: When nothing is clicked for Brand, don't test for Brand.)
• To rephrase, build a table just for this purpose. Focus on it; ignore all other aspects of the data. Even if the data in this table is redundant, you must optimize this table.

Filter rows by count of two column values

You can just add WHERE status = 'E0' inside your subquery:

SQL Fiddle (credit to Raging Bull for the fiddle)

SELECT *
FROM (
    SELECT *,
        COUNT(MembershipNumber) OVER(PARTITION BY EmbossName) AS cnt
    FROM card
    WHERE status = 'E0'
)A
WHERE cnt > 1 

SQL: Multiple count statements with different criteria

SELECT  a.code,
        COALESCE(b.totalNotXorD, 0 ) totalNotXorD,
        COALESCE(c.totalXorD, 0 ) totalXorD,
FROM    (SELECT DISTINCT Code FROM tableName) a
        LEFT JOIN
        (
            select code, count(*) totalNotXorD
            from table 
            where status not in('X','D') 
            group by code
        ) b ON a.code = b.code
        LEFT JOIN
        (
            select code, count(*) totalXorD
            from table 
            where status in('X','D') 
              and cancel_date >= '2012-02-01' 
            group by code
        ) c ON a.code = c.code

or simply doing CASE

SELECT  Code,
        SUM(CASE WHEN status NOT IN ('X','D') OR status IS NULL THEN 1 ELSE 0 END) TotalNotXorD,
        SUM(CASE WHEN status IN ('X','D') AND cancel_date >= '2012-02-01' THEN 1 ELSE 0 END) TotalXorD  
FROM    tableName
GROUP   BY Code

How to count by filter in SQL query?

SELECT COUNT(t1.id) AS all_records,
       SUM(CASE WHEN t1.status = 'completed' THEN 1 ELSE 0 END) AS completed_status_count
FROM t1

Something like this should work. A bit confused about your first count though. Maybe you're looking for something a bit more window-function based. I honestly can't tell.

Multiple counts on different tables in same query

A simple solution would be to use those queries as Sub queries, and combine them:

SELECT 
    grps.GroupName,
    grps.GroupSize,
    psts.TotalPosts
FROM (
    select count(pg.personID) as GroupSize, g.GroupName, g.GroupID
    from Group g inner join PersonGroup pg g.GroupID = pg.GroupID
    where LastViewed between @startDate and @enddate and
        g.Type = 0
    group by g.GroupID, g.GroupName
    order by GroupSize) grps
JOIN (
    select count(gp.PostID) as TotalPosts, g.GroupName, g.groupID
    from Group g inner join GroupPost gp on g.GroupID = gp.GroupID
        inner join Post p on gp.PostID = p.PostID
    where g.Type = 0 and
        gp.Created between @startDate and @enddate
    group by g.GroupID, g.GroupName
    order by TotalPosts) psts
ON psts.GroupID = grps.GroupID

---

Related Topics