Calculate Sum values of Columns in SQL Server

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Jan 06, 2022 12:45 AM
Forum: SQL Server
Answer: 1
Views: 1697

Please i have this report that works very well but the problem is that for the selected date the Total label does not appear.

It is suppose to appear after immediately after the displayed selected dates.

that is this code below.

USE [NAMPAKqqq]
GO
/****** Object:  StoredProcedure [dbo].[Total_MealLog]    Script Date: 1/5/2022 11:58:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Total_MealLog]
    @Start VARCHAR(10),
    @End VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 'S/N',*
    FROM
    (
        SELECT DISTINCT DATENAME(dw, Time1)+', ' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPN,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN')*(SELECT MAX(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPNCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULK,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULKCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREY,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREYCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECH,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECHCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOG,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOGCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULK,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULKCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOG,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOGCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEE,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEECOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULK,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULKCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITOR,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITORCOST,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOL,
            (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOLCOST
        FROM MealLog ml
        WHERE Time1 >= @Start and Time1 <= @End
        GROUP BY Time1, Company, Exact
    )t
END

Download FREE API for Word, Excel and PDF in ASP.Net

arjunv

0 1

Reply
ANSWER
User: arjunv
Replied: on Jan 06, 2022 06:23 AM
Modified: on Jan 06, 2022 06:25 AM
Report

Hi Lingers,

Please Create StoredProcedure with below modified SQL Query. pass parameters in code.

SQL

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) 'S/N',*
INTO #Data
FROM
(
    SELECT DISTINCT DATENAME(dw, Time1)+', ' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPN,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN')*(SELECT MAX(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPN') ARPNCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULK,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'ARPNBULK') ARPNBULKCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREY,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'AGGREY') AGGREYCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECH,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'CRIMETECH') CRIMETECHCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOG,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOG') DELOGCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULK,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'DELOGBULK') DELOGBULKCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOG,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'RDELOG') RDELOGCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEE,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEE') TUSKEEGEECOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULK,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'TUSKEEGEEBULK') TUSKEEGEEBULKCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITOR,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'VISITOR') VISITORCOST,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOL,
        (SELECT COUNT(VALUE) FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL')*(SELECT TOP 1 VALUE FROM MealLog WHERE Time1 = ml.Time1 AND Company = 'MOPOL') MOPOLCOST
    FROM MealLog ml
    WHERE Time1 >= '2021-12-21' and Time1 <= '2021-12-22'
    GROUP BY Time1, Company, Exact
)t
	 
SELECT * FROM #Data
UNION ALL 
SELECT '','Total',SUM(ARPN),SUM(ARPNCOST),SUM(ARPNBULK),SUM(ARPNBULKCOST),
SUM(AGGREY),SUM(AGGREYCOST),SUM(CRIMETECH),SUM(CRIMETECHCOST),SUM(DELOG),SUM(DELOGCOST),
SUM(DELOGBULK),SUM(DELOGBULKCOST),SUM(RDELOG),SUM(RDELOGCOST),SUM(TUSKEEGEE),SUM(TUSKEEGEECOST),
SUM(TUSKEEGEEBULK),SUM(TUSKEEGEEBULKCOST),SUM(VISITOR),SUM(VISITORCOST),SUM(MOPOL),SUM(MOPOLCOST)
FROM #Data

DROP TABLE #Data

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate Sum values of Columns in SQL Server

Company

Explore

Follow us

Contact Us