Click here to Skip to main content
15,936,337 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hey hey guys!

Well, I am trying to finish a query that is a little bit complex, but I try in the begging to explain with words and then I share the query with you guys.

-First column - shows me the type of some file(.exe, .pdf, ...)
-Second column - shows me the percentage of that file on the database
-Third column(Here it's where the problem comes) - should show me the size of every type of file and then below the total.

This is the output with this query without giving me the size of each type of files

C#
Filetype | Percentagem | Total (KB)
.pdf        25%         25
.dll        45%         300
                        170


This is my query:
SQL
SELECT Filetype,
((COUNT(Filetype) * 100) / (SELECT COUNT(*) FROM infofile)) AS Percentagem,
NULL AS 'Total (KB)'
FROM infofile
GROUP BY Filetype
UNION ALL
SELECT NULL,
NULL, 
SUM(Filesize)
FROM infofile


What I want to get to is the size of each Filetype in the Total(KB) column.

Thank you for your help. Have a great day

What I have tried:

I've tried to simulate adding another select that actually works but independent from the main query.

SELECT Filetype, SUM(Filesize) AS 'Total(KB)'
FROM infofile
GROUP BY Filetype
Posted
Updated 3-May-16 10:37am
v2
Comments
CHill60 3-May-16 12:36pm    
What is your table schema?
Member 12490691 3-May-16 12:38pm    
How can I post a print here?
CHill60 3-May-16 12:39pm    
You can't - just list the columns and their types
Member 12490691 3-May-16 12:42pm    
What you clearly want?
CHill60 3-May-16 12:45pm    
I guessing that your table contains
FileType int,
FileSize int,
... something else.
Also some sample data (5 or 6 rows only) of what is on the table - not the output from your query

1 solution

hi you can try this

SQL
SELECT id, code,
    (SELECT SUM(id) AS sumid
     FROM tbl_wtr
     WHERE (accid = newtb.id)) AS sumid
FROM
    (SELECT id, code
     FROM tbl_stacc) AS newtb
 
Share this answer
 
v2
Comments
Member 12490691 3-May-16 17:05pm    
Hey. Thank you for your solution. Your query does not apply with the query that I am trying to build.
CHill60 4-May-16 4:39am    
Do the answers on this post help - Column total at the end of SQL pivot query[^]
Member 12490691 4-May-16 4:54am    
What I have in my query is actually working but instead of using that 'NULL AS 'TOTAL(KB)' I would like to use a SELECT to query me what I want in that column.

This is the Select that I want to include in my query
SELECT Filetype, SUM(Filesize) AS 'Total(KB)'
FROM infofile
GROUP BY Filetype

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900