logo
Tags down

shadow

Can I SELECT *, COUNT(*) in the same query?


By : Andrew
Date : October 17 2020, 06:10 PM
help you fix your problem Your queries seem way too complicated -- too many subqueries. If you have a visit id (that is, a primary key on the table), you can do:
code :
SELECT COUNT(DISTINCT v.visit_id), SUM(o.ipay) TotalIPay
FROM visit v LEFT JOIN
     outcome o
     ON v.vdate = o.vdate
WHERE v.vdate >= 20190101 AND v.vdate <= 20190331;
SELECT MAX(num_visits), SUM(o.ipay) TotalIPay
FROM (SELECT v.*, COUNT(*) OVER () as num_visits
      FROM visit v
      WHERE v.vdate >= 20190101 AND v.vdate <= 20190331
     ) v LEFT JOIN
     outcome o
     ON v.vdate = o.vdate;


Share : facebook icon twitter icon

Select Query- exclude holidays count from a totaldays count obtain from date range


By : Naren Sharma
Date : March 29 2020, 07:55 AM
I hope this helps you . I have a project table having field as name,startdate,enddate, using datediff I calculated totaldays and then filter sundays as colname TotalDay_sundayremoved. , Try this one -
code :
SELECT
      name
    , start_date_pj
    , enddate
    , No_Days = DATEDIFF(DAY, start_date_pj, enddate) 
    , TotalDay_sundayremoved = (DATEDIFF(dd, start_date_pj, enddate) + 1)
         - (DATEDIFF(wk, start_date_pj, enddate) * 1)
         - (CASE WHEN DATENAME(dw, start_date_pj) = 'Sunday' THEN 1 ELSE 0 END) 
        -- - holidays
    , holidays
FROM (
    SELECT 
          name
        , start_date_pj
        , enddate = ISNULL(ed_date_pj, expectedend_date_pj)
    FROM dbo.Project_pj
) p
OUTER APPLY (
    SELECT holidays = COUNT(1)
    FROM dbo.holiday_hd h
    WHERE h.date_hd BETWEEN p.start_date_pj AND p.enddate
) t
SELECT
      name
    , ...
    , holidays = (
        SELECT holidays = COUNT(1)
        FROM dbo.holiday_hd h
        WHERE h.date_hd BETWEEN p.start_date_pj AND p.enddate
      )
FROM (
    SELECT 
          name
        , start_date_pj
        , enddate = ISNULL(ed_date_pj, expectedend_date_pj)
    FROM dbo.Project_pj
) p
SELECT
      name
    , start_date_pj
    , enddate
    , No_Days = DATEDIFF(DAY, start_date_pj, enddate) 
    , TotalDay_sundayremoved = (DATEDIFF(dd, start_date_pj, enddate) + 1)
         - (DATEDIFF(wk, start_date_pj, enddate) * 1)
         - isSunday
        -- - holidays
    , TotalDay_holidayRmoved = (DATEDIFF(dd, start_date_pj, enddate) + 1)
         - (DATEDIFF(wk, start_date_pj, enddate) * 1)
         - isSunday
         - t.holidays
    , t.holidays
    , currentDays_removeSunday = (DATEDIFF(dd, start_date_pj, GETDATE()) + 1)
                 - (DATEDIFF(wk, start_date_pj, GETDATE()) * 1)
                 - isSunday
    , holidays_currentDateCount= holidays_now

    , Currentday_Removeholiday_RemoveSundays_tilldate= DATEDIFF(dd, start_date_pj,  GETDATE()+1) 
                        -(DATEDIFF(wk, start_date_pj, GETDATE()) * 1)
                        - isSunday
                        - holidays_now
FROM (
    SELECT 
          name
        , start_date_pj
        , enddate = ISNULL(ed_date_pj, expectedend_date_pj)
        , isSunday = CASE WHEN DATENAME(dw, start_date_pj) = 'Sunday' THEN 1 ELSE 0 END
    FROM dbo.Project_pj
) p
OUTER APPLY (
    SELECT 
          holidays = SUM(holidays) 
        , holidays_now = SUM(holidays_now)
    FROM (
        SELECT 
              holidays = CASE WHEN h.date_hd BETWEEN p.start_date_pj AND p.enddate THEN 1 ELSE 0 END
            , holidays_now = CASE WHEN h.date_hd BETWEEN p.start_date_pj AND GETDATE() THEN 1 ELSE 0 END
        FROM dbo.holiday_hd h
    ) t
) t

How to get count of this query - Select Count (*) from (select AccountId, * from Account) as CountRecord


By : Peter Pan
Date : March 29 2020, 07:55 AM
around this issue How to get Count of this query ?
code :
Select Count(*) as CountRecord from Account

select count(*) and count(*) where column is not null in single query


By : Denise
Date : March 29 2020, 07:55 AM
wish helps you You seem to be new to SQL. You can easily count the non-NULL values of a column using COUNT():
code :
select count(*), count(address)
from stores;

Select Group by count and distinct count in same mongodb query


By : Onye BF
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further You are beginning to think along the right lines here as you were headed in the right direction. Changing your SQL mindset, "distinct" is really just another way of writing a $group operation in either language. That means you have two group operations happening here and, in aggregation pipeline terms, two pipeline stages.
Just with simplified documents to visualize:
code :
{
    "campaign_id": "A",
    "campaign_name": "A",
    "subscriber_id": "123"
},
{
    "campaign_id": "A",
    "campaign_name": "A",
    "subscriber_id": "123"
},
{
    "campaign_id": "A",
    "campaign_name": "A",
    "subscriber_id": "456"
}
db.campaigns.aggregate([
    { "$match": { "subscriber_id": { "$ne": null }}},

    // Count all occurrences
    { "$group": {
        "_id": {
            "campaign_id": "$campaign_id",
            "campaign_name": "$campaign_name",
            "subscriber_id": "$subscriber_id"
        },
        "count": { "$sum": 1 }
    }},

    // Sum all occurrences and count distinct
    { "$group": {
        "_id": {
            "campaign_id": "$_id.campaign_id",
            "campaign_name": "$_id.campaign_name"
        },
        "totalCount": { "$sum": "$count" },
        "distinctCount": { "$sum": 1 }
    }}
])
{ 
    "_id" : { 
        "campaign_id" : "A", 
        "campaign_name" : "A", 
        "subscriber_id" : "456"
    }, 
    "count" : 1 
}
{ 
    "_id" : { 
        "campaign_id" : "A", 
        "campaign_name" : "A", 
        "subscriber_id" : "123"
    }, 
    "count" : 2
}
{ 
    "_id" : { 
        "campaign_id" : "A", 
        "campaign_name" : "A"
    },
    "totalCount" : 3,
    "distinctCount" : 2
}

Using Access VBA, how to count number of output records in a select query and store the count as a VBA variable?


By : Devina
Date : March 29 2020, 07:55 AM
like below fixes the issue I obtained the solution from this question, to which I was directed by Bernie.
Assuming that the SQL query returns one record, with only one field, and we want that single value:
code :
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT x FROM y WHERE z=z;"
Set rst = CurrentDb.OpenRecordset(strSQL)

VariableName = rst.Fields(0)

rst.Close
Set rst = Nothing
Related Posts Related Posts :
  • SQL Server line break for CSV
  • I need to select clients who have multiple records in one column
  • How to group database column?
  • Getting a specific output from 2 tables
  • How to query different tables for data in order
  • About subquery in T-SQL
  • Memory Buffer pool taken by a Table
  • SQL : ORA-00906: missing left parenthesis
  • Data Conversion error while inserting into a new table from a joint statement
  • How to do sql pivot with multiple values in single column
  • Multiple rows into columns with column values group by columns in SQL Server
  • Data of previous quarters based on sysdate
  • Rearrange Date filter in stored procedure
  • ORA-00942: table or view does not exist .or We are not able to drop
  • SQL Where date1 is 3 days bigger than date2
  • #1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' in MySQL
  • PLS-00642: local collection types not allowed in SQL statements. i am using oracle datadase 12c
  • How can I convert an integer representing EPOCH time to a timestamp in Athena (Presto)?
  • Find the One Random Set_id from each unique Skill_id
  • SQL ORACLE ORA-00923: FROM keyword not found where expected
  • implement a manual increment with just simple SQL
  • SYSDATETIMEOFFSET() to turn off the offset
  • If I query from V$SQL view, does it include SQL that are still running and not yet finished?
  • Is there a way to join results from two queries in Python using sqlalchemy engine?
  • What is the query to choose the number of people living at each address?
  • Filter By date with format 'dd/mm'
  • Listagg in query having aliases
  • SQL stored procedure error about int while there is no int
  • Selecting columns from string
  • Select * parent records that have their children meet some criteria
  • Identity specification
  • Excel: Execute SQL Procedure with VBA -- allow user to continue working -- do not hang the Excel Application
  • sql - single line per distinct values in a given column
  • Select from a select result using lag
  • Window analytic function where window frame and order are different fields?
  • How can i output table data (multiple rows) as a multiple rows of concatenated strings
  • SQL Replace Function Replacing Too Many Parts Of A String
  • How do I have a composite field set in JOOQ
  • Using cases on PS Query
  • Data Truncation issue while importing excel from Azure Blob storage to Sql Server
  • How add aggregate function value in where clause?
  • ROW_NUMBER() OVER is slow with ORDER BY on single column
  • Shifting data from one column to another
  • Prevent XML empty tag parsing to zero
  • How to convert a datetime into date without changing the data type in SQL Server?
  • How to run a query from SQL in RJDBC Oracle Database?
  • Manually Checking of Value Changes in Tables for SQL
  • How can one count total records AND separately count records where two fields are equal
  • Trying to remove a bit.ly URL at the end of a SQL string
  • Variable values stored outside of SSIS
  • How to stop the insertion of default date format 1900-01-01 in SQL Server when user input is null
  • Extraxt date from ISO Week (201905) BigQuery
  • Common table expressions (CTEs) with large tables
  • Boolean type - oracle
  • Update column values sequentially where the new value is based on updated values in a 'previous' row
  • Defining Dates at the Top of Query
  • Why is the table view not coming?
  • in my query When RowNum =1 does not get null value
  • Find out the total call duration happened between 2 people
  • SQLQuery for Time In and Time Out attendance in Oracle
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk