Database Discussion Boards

Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs14-May-17 21:35

14-May-17 21:35

Hello
I can't find solution for this simple query.Want to get sum of column of VISIT_FEE that have colum MONTH_ 2 and YEAR_ 1396

My main table is like this:

ID VISIT_DATE  VISIT_TIME  VISIT_FEE   IS_PAY
1   13960124    10:00           300000          1
6   13960208    10:50           2500000         1
7   13960208    11:00           210000          1
8   13960209    10:20           300000          1

and now i use below query:

SELECT
VISIT_FEE,
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,
EXTRACT(DAY FROM VISIT_DATE) AS DAY_

FROM
    MZS_VISIT_REQUEST
WHERE
    IS_PAY = 1

and result is :

VISIT_FEE  YEAR_   MONTH_  DAY_
300000         1396 1   1
2500000        1396 2   2
210000         1396 2   2
300000         1396 2   2

And now i want some of colum VISIT_FEE that have YEAR_ 1396 and MONTH_ 2 and 1
somethings like below table:

FEE    YEAR_   MONTH_  DAY_
300000         1396 1   1
3010000        1396 2   2

Thanks.

modified 15-May-17 23:40pm.

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Maciej Los14-May-17 22:18

Maciej Los

14-May-17 22:18

Please replace:

SQL

EXTRACT(MONTH FROM VISIT_DATE) AS DAY_

with:

SQL

EXTRACT(DAY FROM VISIT_DATE) AS DAY_

and everything should be OK.

For further details, please see: EXTRACT (datetime)

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs15-May-17 18:40

Mostafazs

15-May-17 18:40

Thanks for replay.
but this is just my misspelling and nothings changed on result. my show result table table is correct.
I want to get sum of column VISIT_FEE

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Maciej Los15-May-17 21:03

Maciej Los

15-May-17 21:03

All you need to do is to use [SUM()](https://www.techonthenet.com/oracle/functions/sum.php) - one of [aggragate functions](https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035):

SELECT SUM(VISIT_FEE) AS FEE,
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,
    EXTRACT(DAY FROM VISIT_DATE) AS DAY_
FROM MZS_VISIT_REQUEST
WHERE IS_PAY = 1
GROUP BY EXTRACT(YEAR FROM VISIT_DATE),
    EXTRACT(MONTH FROM VISIT_DATE) ,
    EXTRACT(DAY FROM VISIT_DATE)

Good luck

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs16-May-17 21:04

Mostafazs

16-May-17 21:04

Thanks.

But it's not what i want.
I want sum of each month like:

FEE YEAR_ MONTH_
300000 1396 1
3010000 1396 2

Sum of Fee column if MONTH_ is equal. like above table.

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

anarayane15-May-17 19:30

anarayane

15-May-17 19:30

SELECT
sum(VISIT_FEE) AS VISIT_FEE,
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,

 
FROM
    MZS_VISIT_REQUEST
WHERE
    IS_PAY = 1
GROUP by
EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_

Please try this one

Re: Get sum of column base on equality of values of one row From previous generate request( using SELECT and EXTRACT)

Mostafazs20-May-17 21:46

Mostafazs

20-May-17 21:46

Thanks.
But when running i get below error:

Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS AYEAR_,<br />
    EXTRACT(MONTH FROM VISIT_DATE) AS BMONTH_' at line 10

When i delete last part(ORDER BY), query without any error with below result
FEE YEAR_ MONTH_
3310000 1396 1

is this any things we can do like -->

<br />
SELECT<br />
        SUM(VISIT_FEE) AS FEE,<br />
    EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,<br />
    EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_<br />
FROM<br />
    MZS_VISIT_REQUEST<br />
WHERE<br />
    IS_PAY = 1 AND DISTINCT MONTH_<br />

???

Access SSRS reports from outside network

bunty_bpr11-May-17 6:08

bunty_bpr

11-May-17 6:08

Hi Team

I am new to SSRS and exploring it for my organizational reporting requirements. After configuring dev instance, I am able to login into Report Manager and Report Server via IE using windows os users. But I am unable to understand below items in SSRS
1. Why logout button is not given in Report Server and how user will logout after access report.
2. If business users wants to access reports from outside(not in network) over internet and if I configured instance on public ip, will that work and how they will logout from session.
3.Do I need to create each business user in windows or users configured in Active Directory will work to access reports from outside over internet.
4.Is there any other way to maintain business users in different repository to avoid OS user and active directory.

Please guide me here.

Thanks

BuntyR

Re: Access SSRS reports from outside network

Simon_Whale11-May-17 6:19

Simon_Whale

11-May-17 6:19

Have a read of this

How do I provide access to SSRS Reports to external users where these SSRS Reports have SSAS as the data source.[^]

There is a link in one of the threads that discusses how to configure SSRS to be used externally

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: Access SSRS reports from outside network

Member 1491271312-Aug-20 2:40

Member 14912713

12-Aug-20 2:40

Hi have you found a solution for this issue?

How to read & write data in queue in sql server

Tridip Bhattacharjee3-May-17 23:51

Tridip Bhattacharjee

3-May-17 23:51

anyone can guide me that how to read & write data in queue in sql server.

do i need to create queue first like table ?
queue is persistent object in db like table ?

please post a small example for read & write data in queue in sql server.

tbhattacharjee

Re: How to read & write data in queue in sql server

Victor Nijegorodov4-May-17 1:47

Victor Nijegorodov

4-May-17 1:47

Did you try to google for the queue examples?
Like this one: Building High Performance Queue in Database for storing Orders, Notifications, Tasks
Or some others from Google

Re: How to read & write data in queue in sql server

CHill604-May-17 3:42

CHill60

4-May-17 3:42

The member is question seems to have a pathological aversion to using Google for themselves. Sigh | :sigh:

Problem with installing oracle database

VISWESWARAN19982-May-17 1:13

VISWESWARAN1998

2-May-17 1:13

Good Morning Sir,
I am willing to install oracle database on my computer. So I have downloaded the database from here Oracle Database Software Downloads | Oracle Technology Network | Oracle[^] I found 12c is the latest database and I have downloaded it. Mine is a Windows 10 64 bit Operating System based on x-64 processor so the database will work in my computer since Microsoft Windows x64 (64-bit) is only present.

I created the oracle account and started installing the database, there were few steps prompted to me before installing the database, I've chosen the default settings and moved on. But I got this error,

Failed to add VISWESWARAN\Visweswaran install user to %2% group

I have searched the internet for this error and referred this Configuring Users, Groups and Environments for Oracle Database[^] thread but I found nothing.

Sir, could you please help me
Thank you for your time sir.

Re: Problem with installing oracle database

Victor Nijegorodov2-May-17 8:23

Victor Nijegorodov

2-May-17 8:23

Have a look at Oracle 12c Install on Windows
It is for Windows 7, but I guess, the installation process on Windows 10 should not differ too much.

Re: Problem with installing oracle database

VISWESWARAN19982-May-17 21:14

VISWESWARAN1998

2-May-17 21:14

Thank you sir for your kind reply. But I have tried that and I see the same error message Frown | :(

Re: Problem with installing oracle database

John C Rayan4-May-17 1:58

John C Rayan

4-May-17 1:58

Can you elaborate on this one

I created the oracle account and started installing the database, there were few steps prompted to me before installing the database, I've chosen the default settings and moved on

Re: Problem with installing oracle database

VISWESWARAN19984-May-17 7:31

VISWESWARAN1998

4-May-17 7:31

Sir,
I mean the initial steps for installing the database like

[radio-button] [selected] Install and configure database
[radio-button] Install database

[Back] [Next]

here Install and configure is the default selected option, all I did is clicked next. since I am a beginner in Oracle database, I've selected the default options without modifying various values.

No, sir there is no guest user account in my computer, I have only one account in my PC which is admin

Thank you sir for your time and kind help

Re: Problem with installing oracle database

John C Rayan4-May-17 2:00

John C Rayan

4-May-17 2:00

I believe that you are installing the software as an Administrator. Right?

Problem creating new username in sql language

Member 1316151629-Apr-17 12:08

Member 13161516

29-Apr-17 12:08

After you have registered all the required data show me this message

Re: Problem creating new username in sql language

Mycroft Holmes29-Apr-17 15:50

Mycroft Holmes

29-Apr-17 15:50

These are not the data you are looking for... move along.

Never underestimate the power of human stupidity
RAH

Re: Problem creating new username in sql language

ZurdoDev2-May-17 11:14

ZurdoDev

2-May-17 11:14

What?

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Problem creating new username in sql language

Victor Nijegorodov2-May-17 12:23

Victor Nijegorodov

2-May-17 12:23

Member 13161516 wrote:
After you have registered all the required data show me this message

Could you post the exact message?
Could you explain what and how you did?
Was it in MS SQL Server or in some other DBMS?

how to get metadata of table from database in php?

Member 1314302421-Apr-17 2:37

Member 13143024

21-Apr-17 2:37

give me code to how to get metadata of table from database in php.

naveen kumar

Re: how to get metadata of table from database in php?

Afzaal Ahmad Zeeshan21-Apr-17 3:22

Afzaal Ahmad Zeeshan

21-Apr-17 3:22

We do not provide free code or projects. You should help yourself and try to find the code from here. You are most likely to get a project sample, and work for that.

Try searching, "mysql data definition sql php" on the top right corner.

The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.