Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Sql Query

Posted on 2009-04-06
5
Medium Priority
?
203 Views
Last Modified: 2012-05-06
I have a table called RestChist and need to pull the following feilds:
CK_CHECKNO, CK_STATUS, CK_COVERS,  CK_TIP,  CK_DISCOUNT,  CK_FOOD,  CK_BEVG,  CK_BEER,  CK_WINE,  CK_LIQUOR, CK_OTHER , CK_TAX  and RESTNO

But, I need to do a distinct count on the CK_CHECKNO to give me a count of how many checks by the server
and
Sum CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER as "Items Sold"
and
sum the "items Sold" along with tips, tax minus discounts to get "total"

What I need is to get something like the following

Server         No. of Checks         Items Sold        Tax          Tips       Discounts        Total
1                       10                          $100              $10          $15           $5                  $120
2                        5                           $75                $7.50        $20           $0                 $102.50


Thanks

0
Comment
Question by:huntleyj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 24081274
Defining a problem is half of the solution and you have defined it well.
Look at the following and see if it gives you what you want:

SELECT SERVER, 
       COUNT(CK_CHECKNO),
       SUM(CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER) AS ItemsSolds,
       SUM(CK_TAX) AS Tax,
       SUM(CK_TIP) AS Tips,
       SUM(CK_DISCOUNT) AS Discounts,
       SUM(CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER + CK_TAX + CK_TIP - CK-DISCOUNT) AS Total 
FROM RestChist 
GROUP BY Server

Open in new window

0
 
LVL 3

Author Comment

by:huntleyj
ID: 24081577
Looks like it did the trick. Only I forgot to add that I needed it for a specific date but I should be able to add in that without issue.
0
 
LVL 3

Author Closing Comment

by:huntleyj
ID: 31567211
Worked great
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24081676
If you need it for a specific date, add that as a WHERE clause between lines 8 and 9.  If you need it for more than one date (as in , "by day for a period between mm/dd/yyyy and mm/dd/yyyy", then include the DateClumn in the SELECT right after the SERVER and group by both the SERVER and your datecolumn with the WHERE clause (between the current lines 8 and 9) indicating WHERE yourdatecolumn BETWEEN date1 AND date2.
0
 
LVL 3

Author Comment

by:huntleyj
ID: 24082604
thanks again.
I did the where clause and got the info I think I need. Now I'll take it to the "powers that be" and see if it suits them.
Good to know about the second grouping option incase they want the info that way.
 
Cheers
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question