How to Select All Columns, and a Count(*) in the Same Query

How to select all columns, and a count(*) in the same query

One approach is to do something like the following. This will result in a count(*) result for each line. But beware, there is a Cartesianjoin; if you have many rows like 'foo%' this will perform badly.

select a.cntr, c.*
from CUSTOMER c 
   , (select count(*) cntr
     from customer b
     where b.name like 'foo%' ) a
where c.name like 'foo%'

How to select all columns and count from a table?

A combination of a window function with DISTINCT ON might do what you are looking for:

SELECT DISTINCT ON (hash_value)
       *, COUNT(*) OVER (PARTITION BY hash_value) AS total_rows
FROM   top_teams_team
-- ORDER  BY hash_value, ???
;

DISTINCT ON is applied after the window function, so Postgres first counts rows per distinct hash_value before picking the first row per group (incl. that count).

The query picks an arbitrary row from each group. If you want a specific one, add ORDER BY expressions accordingly.

This is not "a count of values for the hash_value column" but a count of rows per distinct hash_value. I guess that's what you meant.

Detailed explanation:

• Best way to get result count before LIMIT was applied
• Select first row in each GROUP BY group?

Depending on undisclosed information there may be (much) faster query styles ...

• Optimize GROUP BY query to retrieve latest row per user

Need to select ALL columns while using COUNT/Group By

I think a more general solution is to use windows functions:

select *
from (select *, count(*) over (partition by dob) as NumDOB
      from table
     ) t
where numDOB > 1

The reason this is more general is because it is easy to change to duplicates across two or more columns.

How to select columns and count(*)? in SQL

Something like this should do things for you (with GROUP BY):

SELECT  u.idUser,
        u.nameUser,
        COUNT(DISTINCT m.idChat) as countChats
FROM [User] u
LEFT JOIN Members m
    ON u.idUser = m.idUser
GROUP BY u.idUser, u.nameUser

Or with PARTITION BY

SELECT DISTINCT 
        u.idUser,
        u.nameUser,
        COUNT(m.idChat) OVER (PARTITION BY nameUser) as countChats
FROM [User] u
LEFT JOIN Members m
    ON u.idUser = m.idUser

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 *, count(*) in one Query

You can do it with a correlated sub-query, Count is an aggregation function ( so it aggregates or combines all the data ):

$query = "
    SELECT
        t1.*,
        ( SELECT COUNT(t0.id) FROM test AS t0 WHERE t0.id = t1.id ) AS total_example_A
    FROM
        test AS t1
    WHERE
            t1.example_A = 'certain_result'
        AND
            date(t1.start_date) = '$current_date_proof'
    ORDER BY t1.start_date ASC
";

This assumes that your table test has a primary key named id. One other thing is I would count on the primary key if its not (example_A) COUNT(t0.id)

In my world a database either have a Auto Increment Int as the primary key or they have a compound primary key consisting of 2 or more foreign keys which are themselves Auto Increment Int fields. It's vital ( IMO ) to always have a surrogate key in you table. That is a key that has no direct relationship to the data itself. But, that is just me...

You could just count the return within your application, but barring that the correlated sub-query should give you the best/goodest performance. Certainly much better then a separate database call.

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

Select multiple count(*) in multiple tables with single query

A more traditional approach is to use "derived tables" (subqueries) so that the counts are performed before joins multiply the rows. Using left joins allows for all id's in basic to be returned by the query even if there are no related rows in either joined tables.

select
      basic.id
    , coalesce(a.LinkACount,0) LinkACount
    , coalesce(b.linkBCount,0) linkBCount
from basic 
left join (
     select id, Count(linkA_ID) LinkACount from LinkA group by id
     ) as a on a.id=basic.id
left join (
     select id, Count(linkB_ID) LinkBCount from LinkB group by id
     ) as b on b.id=basic.id

---

Related Topics